PowerQuery transformation

Hi powerbricco ,

The answer you received is spot on and covers both the step-by-step method and a ready-to-use M code solution for your Power Query transformation.

To add a few clarifications:

• If your headers are not recognized, use “Use First Row as Headers” in Power Query. This is important for the subsequent steps to work correctly.
• When you unpivot the date columns, you’ll convert your wide table into a long format, which is exactly what you need for the “Desired Table”.
• Filtering for only SUM rows is crucial, since you only want those in your final output.
• The provided M code is a great shortcut if you’re comfortable with the Advanced Editor. Just replace "Your Previous Step" with the actual previous step name in your query.

Here’s a quick summary of the M code approach:

let
    Source = #"Your Previous Step", // Replace with your actual step name
    #"Removed Columns" = Table.RemoveColumns(Source,{"Source.Name"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"ART"}, "Date", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([ART] = "SUM")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

With this, you’ll get exactly the output format you showed as “Desired Table”.

If you have trouble with any of the steps or need help adapting the M code to your actual table name, just let me know!

Good luck with your Power Query transformation!
translation and formatting supported by AI

burakkaragoz  and Elena_Kalina 
The solution you provided wont work because each source has different date columns.
If you make the top row a header or unpivot the whole table as you have kindly suggested then it produces the wrong answer.
Try it and share a PBIX if you think it works. Thanks