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

Transpose only selected rows into columns (for over 190 columns)

Hello,

I have a table with the following data. The dataset has over 190 columns.  Each column has top 7 rows that are actually query parameters set while extracting this data from another system (Entity, Activity, Year, Month, Scenario, Version, Product) and the first 2 columns of my data set are used for DeptID and Account Number. I'd like to format this table in Power Query Editor, and make each of the top 7 rows their own columns. See the 2nd screenshot for intended result.

Problem DatasetProblem DatasetIntended ResultIntended Result

 

I have been making unsuccessful attempts and tried many combinations of transpose, pivot/unpivot. Any help would be appreciated!

Thanks in advance!

1 ACCEPTED SOLUTION
mcybulski
Solution Specialist
Solution Specialist

Someone is using Essbase 😁

 

Start by Merging the Product ID and Departments by a delimiter that is not in your data. I picked pipe (|).

Transpose the table. Promote headers and rename the Entity through Product columns.

Now unpivot the other columns (the data details).

Split the Product ID and Department column on the pipe

Rename the split columns.

Below shows how it works.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"|", "Entity"}, {"|_1", "Activity"}, {"|_2", "Year"}, {"|_3", "Month"}, {"|_4", "Scenario"}, {"|_5", "Version"}, {"|_6", "Product"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Product", "Version", "Scenario", "Month", "Year", "Activity", "Entity"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "DeptID"}, {"Attribute.2", "Account"}, {"Value", "Amount"}})
in
    #"Renamed Columns1"

 

Hope this helps!

 

View solution in original post

3 REPLIES 3
mcybulski
Solution Specialist
Solution Specialist

Someone is using Essbase 😁

 

Start by Merging the Product ID and Departments by a delimiter that is not in your data. I picked pipe (|).

Transpose the table. Promote headers and rename the Entity through Product columns.

Now unpivot the other columns (the data details).

Split the Product ID and Department column on the pipe

Rename the split columns.

Below shows how it works.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"|", "Entity"}, {"|_1", "Activity"}, {"|_2", "Year"}, {"|_3", "Month"}, {"|_4", "Scenario"}, {"|_5", "Version"}, {"|_6", "Product"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Product", "Version", "Scenario", "Month", "Year", "Activity", "Entity"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "DeptID"}, {"Attribute.2", "Account"}, {"Value", "Amount"}})
in
    #"Renamed Columns1"

 

Hope this helps!

 

@mcybulski You're awesome!! I was missing the merged part in the beginning and therefore struggling to reach the solution. Thank you so much for your quick response! This has resolved the issue! Much Appreciated!! 🙂 

You are welcome chandakaushik. It is fun to see that I am not the only person who looks at the Essbase data and wants it in a data format!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!