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!

 

View solution in original post

@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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors