Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to use power query to consolidate columns, reverse context

Team,

 

I am looking to see if and how, Power Query can be leveraged to convert the below table where I get a column for Rank and Revenue for each customer each year from our system to just four columns of CUSTOMER, RANK, REVENUE, YEAR.

 

WHAT I GET FROM MY SYSTEM OUTPUT (I get one row for a customer across all columns)

 

What I need to convert it to:

 

NEW TABLE (Where I repeat the customer each year and don't have columns for every year)

  • CUSTOMER
  • YEAR
  • RANK
  • REVENUE

 

I greatly appreciate any assistance that can be provided that points me in a direction of solving this 

Need Help
  • camargos88's avatar
    camargos88
    6 years ago

    Hi Anonymous ,

     

    Check this file: Download PBIX 

     

    Basically you need to select the CORPORATE_NAME and unpivot other columns, split the Attributes column by "_" and

    pivot the Attribute.1 but don't aggregate the Value column.

     

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    I extracted the values from the image you uploaded and came up with the below M query to accomplish the transform you are looking for as an example.  Please paste this into a blank query to see.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc89C8MgEIDh/+KcQc/PjqE4FRwK7SISHDKmCaFL/33vLB081+cOvTdncd3PYz/re11S3VYxIWxHfX1mUaYs7nO6LSCVxoHtxKCYTiyK7sShQCceRXUShp0Lfwck/wsUvwcAxXVCN/ufxGdMj/gPUVJyphoYmZLAAOfWBQO3OKU4U6HWmjNlGmMYt1ZrLWcKds5xpmrveWVLDyGIUr4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t, Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}, {"Values", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Corporate_Name", each if [Data]="Corporate_Name" then [Values] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Corporate_Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Data] <> "Corporate_Name")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Corporate_Name", "Data", "Values"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Data", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Data.1", "Data.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Data.2", "Year"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Data.1]), "Data.1", "Values"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"REVENUE", Int64.Type}, {"RANK", Int64.Type}, {"Year", Int64.Type}, {"Corporate_Name", type text}})
    in
    #"Changed Type2"

     

    If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.
    Regards,
    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much!  This worked swimmingly.  Advanced my query editing a bit tonight!

      mahoneypat 

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous ,

     

    Check this file: Download PBIX 

     

    Basically you need to select the CORPORATE_NAME and unpivot other columns, split the Attributes column by "_" and

    pivot the Attribute.1 but don't aggregate the Value column.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much!  This worked swimmingly.  Advanced my query editing a bit tonight! camargos88  Very efficient!