Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kartiklal70
Frequent Visitor

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

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_2-1672331813219.png

 

 

kartiklal70_1-1672331687514.png

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
kartiklal70
Frequent Visitor

Please see link for a file with some sample data:

 

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur ,

 

Thank you. This was exactly what I needed. 

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yueyunzh-msft
Community Support
Community Support

Hi , @kartiklal70 

According to your description, you want to pivot the table.

This is my test data:

vyueyunzhmsft_0-1672367518610.png

Do you mean you want to get this table:

vyueyunzhmsft_1-1672367536011.png

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

@v-yueyunzh-msft ,

 

Thank you for the solution. Yes, I want to get to this table. 

kartiklal70_0-1672401515363.png

 

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 :

vyueyunzhmsft_0-1672630233192.png

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:

vyueyunzhmsft_1-1672630283065.png

 

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 :

vyueyunzhmsft_0-1672630233192.png

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:

vyueyunzhmsft_1-1672630283065.png

 

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
Resolver III
Resolver III

Hi @kartiklal70 ,

can you send us a sample data to work with?

or you can copy here in table format.

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.