Forum Discussion
Complex Pivot Column
Hello Everyone,
I've been looking for a few days now how to perform the following transformation from Table1 to Table2 but it seems awfully specific and i even have trouble explaining with words what i really want so i'll just expose the table I get and the table i try to get:
Table1:
| InstanceID | TaskName | startDate_task | endDate_task |
| bidule | A | 01/06/23 | 02/06/23 |
| bidule | B | 04/06/23 | 06/06/23 |
| bidule | C | 08/06/23 | 15/06/23 |
| chouette | A | 06/06/23 | 07/06/23 |
| chouette | B | 11/06/23 | 12/06/23 |
| chouette | C | 13/06/23 | 18/06/23 |
| chouette | D | 23/06/23 | 24/06/23 |
| machin | A | 01/06/23 | 02/06/23 |
| machin | B | 01/06/23 | 03/06/23 |
| machin | B | 04/06/23 | 06/06/23 |
table2:
| instanceID | stardDate_task_A | endDate_task_A | stardDate_task_B | endDate_task_B | stardDate_task_C | endDate_task_C | stardDate_task_D | endDate_task_D |
| bidule | 01/06/23 | 02/06/23 | 04/06/23 | 06/06/23 | 08/06/23 | 15/06/23 | ||
| chouette | 06/06/23 | 07/06/23 | 11/06/23 | 12/06/23 | 13/06/23 | 18/06/23 | 23/06/23 | 24/06/23 |
| machin | 01/06/23 | 02/06/23 | 04/06/23 | 06/06/23 |
For instanceID "machin" I want only the latest task to be selected but I figured that I should prior to pivot the columns deduplicate these repeated task by choosing the latest one but this is an other problem i have not investigated yet.
The endgame for doing this transformation is to be able to calculate easily duration between any tasks as it is the KPIs i try to get in my final report. As i write this post, I wonder now if in terms of performance it would be better to keep table1 and try to get the KPIs wanted with DAX, if you have an opinion on this I would gladly hear it also 🙂
Thank you in advance for every bit of help you will give me
Best regards
Bonjour Ilovechocolate
Je vais répondre en français, "bidule", "machin" "chouette" 🙂
Même réponse qu'hier (https://community.fabric.microsoft.com/t5/Power-Query/Transform-data-with-Pivot-function-for-multiple-value-columns/td-p/3307920) sauf le "max" à la fin
C'est un classique dépivot, fusion de colonne, pivot (dans le pivot, il faut aller dans les options avancées et choisir "max" pour obtenir la dernière occurrence)
J
let
Source = VotreSource,
Unpivot = Table.Unpivot(Source, {"startDate_task", "endDate_task"}, "Attribut", "Valeur"),
CombineColumns = Table.CombineColumns(Unpivot,{"Attribut", "TaskName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Task"),
Pivot = Table.Pivot(CombineColumns, List.Distinct(CombineColumns[Task]), "Task", "Valeur", List.Max)
in
PivotVoilà pour la première partie de la question.
Pour le reste, il faudrait plus de détail pour le calcul de l'écart entre tâche (écart en jour calendaire, en jour ouvré).
Stéphane
6 Replies
Bonjour Ilovechocolate
Je vais répondre en français, "bidule", "machin" "chouette" 🙂
Même réponse qu'hier (https://community.fabric.microsoft.com/t5/Power-Query/Transform-data-with-Pivot-function-for-multiple-value-columns/td-p/3307920) sauf le "max" à la fin
C'est un classique dépivot, fusion de colonne, pivot (dans le pivot, il faut aller dans les options avancées et choisir "max" pour obtenir la dernière occurrence)
J
let
Source = VotreSource,
Unpivot = Table.Unpivot(Source, {"startDate_task", "endDate_task"}, "Attribut", "Valeur"),
CombineColumns = Table.CombineColumns(Unpivot,{"Attribut", "TaskName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Task"),
Pivot = Table.Pivot(CombineColumns, List.Distinct(CombineColumns[Task]), "Task", "Valeur", List.Max)
in
PivotVoilà pour la première partie de la question.
Pour le reste, il faudrait plus de détail pour le calcul de l'écart entre tâche (écart en jour calendaire, en jour ouvré).
Stéphane
Bonjour Stéphane,
Merci beaucoup pour la méthodo c'est beaucoup plus clair, j'étais loin de trouver la solution! Du coup, en règle général je comprends que en première étape il faut tout détailler en "lignes" les attributs que je voudrais ensuite voir en colonnes. Ensuite je fusionne les attributs avec la colonne "clé" que je veux pivoter et enfin je pivote cette colonne concaténée et les valeurs dans cette colonne deviennent mes en-têtes de colonnes.
Pour la deuxième partie j'étais plus sur un écart en jours calendaires donc très simple à faire, mais j'ai choisi ça parce que je ne savais pas que on pouvait faire en jour ouvré, je suppose qu'il me faut une autre requête avec le calendrier français et ses jours fériés?
Cordialement,
Cordialement,
Merci pour le retour
pour les écarts, on peut tout faire (en calendaire ou en jours ouvrés - avec en effet une table des jours fériés du pays concerné).
il faut aussi savoir comment faire l'écart entre 2 tâches : sont-elles triées dans la source ? je suppose qu'elles ne s'appellent pas réellement A, B, C
Sinon, sur le fond, je trouve qu'il est préréfable de travailler avec la base initiale et de ne pas pivoter les colonnes pour calculer les écarts (en travaillant par regroupement pour prendre le max de la tâche B sur l'instance "machin")
Stéphane
Ok je vois, du coup tu partirais plutôt sur du DAX pour traiter ce genre de table ? Ou en règle général tu essayes de toucher le moins possibles aux tables dans PowerQuery? C'est une question que je me pose régulièrement, comme je ne travaille pas sur des énormes datasets je ne vois pas trop d'écart de performance pour l'instant entre faire beaucoup de pré-traitement dans PowerQuery et construire des mesures en DAX.
Pour ce qui est des écarts effectivement, les tâches ne sont pas dans l'ordre, mais je cherche des durées entre des tâches précises donc je vais par exemple créer une colonne "durée activité 1" qui correspond à la différence entre la fin de la tâche A et au début de la tâche C ce qui sera assez aisé avec le tableau pivoté
M ou DAX : ça dépend, du besoin, de la volumétrie, du temps de calcul (à l'actualisation ou à la volée), ...
je travaille beaucoup sur Excel, je maitrise mieux le M que DAX, j'ai donc tendance à le faire en M.
Mais dans Power BI, je teste différentes solutions et j'utilise M/DAX suivant l'efficacité de chacun.
Stéphane
Ok merci pour ces réponses Stéphane, bonne journée!
