Forum Discussion

kartiklal70's avatar
kartiklal70
Frequent Visitor
3 years ago
Solved

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). 

 

 

 

 

Need Help

9 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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