Forum Discussion
Solved
PowerQuery transformation
Dear All, I have this kind of table in powerquery: Original Table Source.Name ART 15/01/2025 14/01/2025 13/01/2025 12/01/2025 11/01/2025 10/01/2025 09/01/2025 08/01/2025 07/01/2025...
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:
mlet 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 AIburakkaragoz 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
Hi powerbricco ,
You can refer to steps as below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZY7bsMwDIbv4jloSIqU5LEHaIemnYoMaZGxDwTo/SvSkczFmzQYBmhRovyT5gfZ7+8TTofp9PZUbMBYLM86kwGKTWxWsq5SUsuiM5E1JmmkiMZInnWvhGJRNEZAYzjKdD4sKr/Xy63cKOsGWmJsmC1S1CJTsZYCc7IH6JhmSy3p47MNNYxgWaMm8XH5LpfmaUFRbTARlmVsIlksA7aiTQWhzaC9hhCTjYPtsrKKuuqcfv5un9eH58vXtcw+vrzqWjwCHgmsKhTvsHeCd8g76B1wDszeyd5J3vEZgM8AfAbgM4A1Ay2MGgkRZQgPVeVOAiN356FKNBIWSLrzsEmCb8oesdDCQiNBrOP9eagqdxKCBfXloUo0EnCGETxskuCbskcstDBuJLC9vf48VJX6dVj60JeHqrEeCoFHALGJgu/KHrnQwqShkAmGAFFV6uchpAFAVJH1VyGHEURssUC+LXsEQwuL67FAPISIqnJnAY2F3kRUkfVcyGkEEefzPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, ART = _t, #"15/01/2025" = _t, #"14/01/2025" = _t, #"13/01/2025" = _t, #"12/01/2025" = _t, #"11/01/2025" = _t, #"10/01/2025" = _t, #"09/01/2025" = _t, #"08/01/2025" = _t, #"07/01/2025" = _t, #"06/01/2025" = _t, #"05/01/2025" = _t, #"04/01/2025" = _t, #"03/01/2025" = _t, #"02/01/2025" = _t, #"01/01/2025" = _t]),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type", "Source.Name", each if [Column1] = "Source.Name" then null else [Column1]),
#"Filled Up" = Table.FillUp(#"Added Conditional Column1",{"Source.Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"Column1"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column2","Source.Name"}, "Attribute", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Custom1", each if Text.Contains([Value], "/") then [Value] else null),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom", each let _SN = [Source.Name], _Attribute = [Attribute] in Table.SelectRows(#"Added Conditional Column",each [Source.Name] = _SN and [Attribute] = _Attribute and [Custom1] = null)[Value]),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom1] <> null)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column2", "Source.Name", "Attribute", "Value"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "ART", each "SUM"),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"ART", "Custom1", "Custom"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Custom1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom1.1", "Custom1.2", "Custom1.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom1.1", Int64.Type}, {"Custom1.2", Int64.Type}, {"Custom1.3", Int64.Type}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"ART", "Custom1.2", "Custom1.1", "Custom1.3", "Custom"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns1", {{"Custom1.2", type text}, {"Custom1.1", type text}, {"Custom1.3", type text}}, "zh-CN"),{"Custom1.2", "Custom1.1", "Custom1.3"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Date"),
#"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Custom", "Value"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Value", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Date", Order.Ascending}})
in
#"Sorted Rows"Original:
Result is as below.
Best Regards.
Rico Zhou