How to use power query to consolidate columns, reverse context

[Anonymous]

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 

[camargos88 · Accepted solution]

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]

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

[mahoneypat]

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]

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

@mahoneypat