Forum Discussion
Pivot Rows to Columns and Concatenate These New Columns with Two Existing Columns
Hi , kartiklal70
According to your description, you want to pivot the table.
This is my test data:
Do you mean you want to get this table:
If this , you can put this M code in "Advanced Editor":
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBNDoIwEEavQromGRmgyJJwASNLwgKxMU2gRX48v5W0OIlC2H3TeZ15bVmyCIIY8IQB81mAtkBTFFcvH0Q9iTur/JKZ89BxESC6nDXPWY6T1MrLddcJ1dgLKWDqZtFc6HZe8KzvB/2ytGmYkdEHCk2+tLVSUj28Yr51chzt+L4Vk1h4qs0BN6QpRfOmNCevTIC7+N/5DCtL4hF1Tn98XfLjnrpWDMkRdfyq4746hWnel6/e", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Actual Date" = _t, #"Forecast Date" = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Actual Date", type date}, {"Forecast Date", type date}, {"Name", type text}}),
Custom1 = Table.Group(#"Changed Type","Name",{{"Actual",(x)=>x[Actual Date] },{"Forecast",(y)=>y[Forecast Date]}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Custom1, {"Name"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Name", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
Custom2 = Table.TransformColumns( #"Merged Columns",{"Value",(x)=> Table.AddIndexColumn( Table.Sort(Table.FromColumns({x}),"Column1"), "Index", 1) }),
#"Expanded Value" = Table.ExpandTableColumn(Custom2, "Value", {"Column1", "Index"}, {"Column1", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Value", List.Distinct(#"Expanded Value"[Merged]), "Merged", "Column1")
in
#"Pivoted Column"Then we can get the table you want to .
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you for the solution. Yes, I want to get to this table.
But I need to make this work some additional columns in the dataset as well. Please see link for a sampe PBI file with all the columns.
https://www.dropbox.com/s/9dcua3u3l1svuyd/Sample%20Power%20BI%20File.pbix?dl=0
Hi , kartiklal70
Thanks for your quick response and sample .pbix file !
For this , you can just remove the columns you do not want and then add the M code after it.
You can create a "Blank Query " to test :
And then you can put the M code in the "Advanced Editor":
let Source = #"Sample Data", #"Removed Other Columns" = Table.SelectColumns(Source,{"Forecast Date", "Actual Date", "Name"}), Custom1 = Table.Group( #"Removed Other Columns" ,"Name",{{"Actual",(x)=>x[Actual Date] },{"Forecast",(y)=>y[Forecast Date]}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Custom1, {"Name"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Name", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), Custom2 = Table.TransformColumns( #"Merged Columns",{"Value",(x)=> Table.AddIndexColumn( Table.Sort(Table.FromColumns({x}),"Column1"), "Index", 1) }), #"Expanded Value" = Table.ExpandTableColumn(Custom2, "Value", {"Column1", "Index"}, {"Column1", "Index"}), #"Pivoted Column" = Table.Pivot(#"Expanded Value", List.Distinct(#"Expanded Value"[Merged]), "Merged", "Column1") in #"Pivoted Column"Then we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , kartiklal70
Thanks for your quick response and sample .pbix file !
For this , you can just remove the columns you do not want and then add the M code after it.
You can create a "Blank Query " to test :
And then you can put the M code in the "Advanced Editor":
let Source = #"Sample Data", #"Removed Other Columns" = Table.SelectColumns(Source,{"Forecast Date", "Actual Date", "Name"}), Custom1 = Table.Group( #"Removed Other Columns" ,"Name",{{"Actual",(x)=>x[Actual Date] },{"Forecast",(y)=>y[Forecast Date]}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(Custom1, {"Name"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(#"Unpivoted Columns",{"Name", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), Custom2 = Table.TransformColumns( #"Merged Columns",{"Value",(x)=> Table.AddIndexColumn( Table.Sort(Table.FromColumns({x}),"Column1"), "Index", 1) }), #"Expanded Value" = Table.ExpandTableColumn(Custom2, "Value", {"Column1", "Index"}, {"Column1", "Index"}), #"Pivoted Column" = Table.Pivot(#"Expanded Value", List.Distinct(#"Expanded Value"[Merged]), "Merged", "Column1") in #"Pivoted Column"Then we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
