Index Equiv dans Power Query (Excel) multi critere

Anonyme
2023-02-13T15:56:18+00:00

Colonne A, B, C, D : données disponibles

Colonne E = Colonne calculée : =SI(D2<0;SIERREUR(INDEX($B$2:$B$9;EQUIV(1;($A$2:$A$9=A2)*($B$2:$B$9<>B2)*($C$2:$C$9=C2)*($D$2:$D$9=-D2);0);1);B2);B2)

Objectif de la colonne E : ajouter une colonne "Lieu ajusté". Pour chaque ligne de la table :

  1. Si : "QtéHeures" est plus petit que 1, voir 2, sinon mettre le "Lieu" correspondant à la ligne analysé dans "Lieu ajusté"
  2. **a.** Recherche dans la table la première ligne ayant les critères suivants :

Même "Identifiant", "Lieu" différent, même "Date", "Qté heures" en signe opposé

b. Si résultat de recherche existe : Inscrit le "Lieu" de la ligne trouvé dans la nouvelle colonne "Lieu ajusté"

c. Si résultat obtenu = erreur, alors inscrit le "Lieu" de la ligne analysé

Problème : comment effectuer l'équivalent de l'étape 2 en utilisant Power Query sur un jeu de données plus grand que 200K lignes ?

Identifiant Lieux Date Qté heures Lieu ajusté
ABC TRE565 2022-02-03 9 TRE565
ABC TRE565 2022-02-04 7 TRE565
ABC TRE589 2022-02-04 7 TRE589
ABC TRE565 2022-02-04 -7 TRE589
DEF TRE759 2022-02-01 7 TRE759
DEF TRE759 2022-02-01 -7 TRE059
DEF TRE059 2022-02-01 7 TRE059
DEF TRE759 2022-02-02 7 TRE759
Microsoft 365 et Office | Excel | Autres | Windows

Question verrouillée. Cette question a été migrée à partir de la Communauté Support Microsoft. Vous pouvez voter pour indiquer si elle est utile, mais vous ne pouvez pas ajouter de commentaires ou de réponses ni suivre la question.

0 commentaires

Réponse acceptée par l’auteur de la question

Hecatonchire 53,870 Points de réputation Modérateur bénévole
2023-02-13T19:18:58+00:00

Bonjour,

Requête :

let 

Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content], 

#"Type modifié" = Table.TransformColumnTypes(Source,{{"Identifiant", type text}, {"Lieux", type text}, {"Date", type datetime}, {"Qté heures", Int64.Type}}), 

#"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Type modifié", "Personnalisé", each if [Qté heures] &lt; 0 then try Record.Field(**maFctLieu**([Identifiant],[Lieux],[Qté heures],[Date]){0},"Lieux") otherwise [Lieux] else [Lieux])

in 

#"Colonne conditionnelle ajoutée"

Fonction personnalisée maFctLieu

let 

Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content], 

#"Type modifié" = Table.TransformColumnTypes(Source,{{"Identifiant", type text}, {"Lieux", type text}, {"Date", type datetime}, {"Qté heures", Int64.Type}}), 

#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([Identifiant] = Ident) and ([Lieux] &lt;&gt; Lieu) and ([Date] = DateX) and (([Qté heures] &lt;0) &lt;&gt; (QtH &lt;0))), 

#"Conserver les premières lignes" = Table.FirstN(#"Lignes filtrées",1), 

#"Autres colonnes supprimées" = Table.SelectColumns(#"Conserver les premières lignes",{"Lieux"})

in 

#"Autres colonnes supprimées"

Utilisant ces paramètres

Ident

Lieu

DateX

QtH

Pour info : n'ayant pas de précision sur quoi faire en cas de plusieurs lieux possibles, j'ai pris le 1er trouvé (d'où la ligne n°2 que j'ai ajoutée pour faire des tests de doublon et de non trouvé)

(Je pense que du faite du [Date]){0} de la requête, le #"Conserver les premières lignes" = Table.FirstN(#"Lignes filtrées",1) de la fonction est inutile)

Cette réponse a-t-elle été utile ?

1 personne a trouvé cette réponse utile.
0 commentaires

8 réponses supplémentaires

Trier par : Le plus utile
Le plus utile Les plus récents Les plus anciens
  1. Anonyme
    2023-02-15T15:48:07+00:00

    Bonjour Hecatonchire,

    Merci pour la réponse !! Ça semble parfait !

    J'ai un petit enjeu qui m'empêche de tester votre réponse dans mon code. Je vous reviens (dans le présent forum) d'ici la fin de la semaine avec la confirmation que le code a bien fonctionné.

    Merci encore pour votre temps et expertise !

    Cette réponse a-t-elle été utile ?

    0 commentaires
  2. Hecatonchire 53,870 Points de réputation Modérateur bénévole
    2023-02-15T12:33:09+00:00

    Merci pour ce gentil retour, ca fait toujours plaisir et nous prouve que l'on à pas perdu notre temps pour rien !

    🤣

    Cette réponse a-t-elle été utile ?

    0 commentaires
  3. Anonyme
    2023-02-13T16:55:49+00:00

    Merci pour l'intérêt, mais il me faut une solution avec PQ.

    Au plaisir.

    Cette réponse a-t-elle été utile ?

    0 commentaires
  4. DanielCo 107.7K Points de réputation
    2023-02-13T16:45:36+00:00

    Bonjour,

    C'est possible avec une macro. Avec PQ, je ne sais pas faire.

    Daniel

    Cette réponse a-t-elle été utile ?

    0 commentaires