cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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)

Screen Shot 2020-05-11 at 5.39.01 PM.png

 

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
Super User V

Re: How to use power query to consolidate columns, reverse context

Hi @Smoody07 ,

 

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.

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

4 REPLIES 4
Highlighted
Super User VI
Super User VI

Re: How to use power query to consolidate columns, reverse context

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Super User V
Super User V

Re: How to use power query to consolidate columns, reverse context

Hi @Smoody07 ,

 

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.

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Highlighted
Helper I
Helper I

Re: How to use power query to consolidate columns, reverse context

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

@mahoneypat 

Highlighted
Helper I
Helper I

Re: How to use power query to consolidate columns, reverse context

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors