Forum Discussion
Transpose the 4 most recent dates
Hello everyone,
I hope you are all well.
I am attaching my sample file containing a tabble and Power Query code that is not working properly.
Could you please improve it with a more elegant solution?
Thank you in advance.
Best regards
Your pivot function is incorrect, referencing the wrong columns from the preceding step:
Table.Pivot(#"Colonnes dépivotées", List.Distinct(#"Colonnes dépivotées"[Attribut]), "Attribut", "Valeur")
7 Replies
Your pivot function is incorrect, referencing the wrong columns from the preceding step:
Table.Pivot(#"Colonnes dépivotées", List.Distinct(#"Colonnes dépivotées"[Attribut]), "Attribut", "Valeur")- What is the purpose of reducing the number of steps?
Any other problems with your output in your file? - To obtain the results you can either do the unpivot/pivot process (as in your example) or you could
demote headers/transpose/promote headers - What do you mean by the "four most recent dates"?
The dates you are showing in your results are merely the first four in your table which, because your table is sorted ascending, are merely the first four dates in your table. - Why are you using List.Sum in your pivot code? It seems you only have a single intersection of date/attribut so what are you trying to Sum?
- What is the purpose of reducing the number of steps?
Hello ronrsnfld ,
1. Fewer steps means code that is created from scratch and is often more elegant.
2. Thank you for your two suggestions.
3. The four recent steps correspond chronologically to the last four dates in the column. This is equivalent to taking the 4 last four or first values after sorting the dates in Descending or Ascending.
Indeed, it would have been more logical to use "Order.Ascending" then Table.LastN(prevStep,4).I did the opposite with "Order.Descending" then Table.FirstN(prevStep, 4)
The result is the same.
4. I simply used the ‘Pivot Column’ feature in the Ribbon. This generated this line of code automatically.Best regards
For me, "functional" trumps "elegant". If speed of execution is limiting, then I'd run some tests (you can do this in Power BI Desktop) to see if the pivot method or transpose method is more efficient.
If you don't have multiple entries with the same row and date, you can remove the aggregation function from the Table.Pivot function.
And if the number of rows ("A","B","C","D"} might vary, I'd rewrite a few steps to allow the code to adapt dynamically.
But other than that, I wouldn't bother to change anything.
Thank you for your reply.
Processing speed is indeed an important factor.
By elegance, I mean, of course, that it should be short, readable, and without any loss of quality.
Regarding aggregation, List.Sum is indeed useless here. I removed it and it works just as well.
The text values “A,” “B,” “C,” and “D” will never change; only new dates are added each week.
Otherwise, thank you for your advice. It helped me solve the problem and better understand the subtleties of M.Best regards