Pivot Rows to Columns and Concatenate These New Columns with Two Existing Columns

[kartiklal70]

Hi All, 

I have a dataset in the following format. I'd like to Pivot the values in the "Name" Column into their own separate column (I'm aware of how do this in Power Query) and then concatenate these new columns with Actual And Forecast Date so the resutling columns look as in the 2nd screenshot (not sure how to do this part). 

[kartiklal70]

Please see link for a file with some sample data:

https://www.dropbox.com/s/9dcua3u3l1svuyd/Sample%20Power%20BI%20File.pbix?dl=0

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[kartiklal70]

@Ashish_Mathur ,

Thank you. This was exactly what I needed. 

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[v-yueyunzh-msft]

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

[kartiklal70]

@v-yueyunzh-msft ,

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

[v-yueyunzh-msft]

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

[v-yueyunzh-msft]

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

[grandtotal]

Hi @kartiklal70 ,

can you send us a sample data to work with?

or you can copy here in table format.

Thanks