Forum Discussion

Mederic's avatar
Mederic
Post Patron
6 months ago
Solved

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 soluti...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    6 months ago

    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")

     

ronrsnfld's avatar
ronrsnfld
Super User
6 months ago

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.

 

 

Mederic's avatar
Mederic
Post Patron
6 months ago

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