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

[chandakaushik]

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 DatasetIntended Result

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

Thanks in advance!

[Syndicate_Admin]

Thank you so much. This was really helpful.

[Anonymous · Accepted solution]

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!

[chandakaushik]

@Anonymous 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!! 🙂 

[Anonymous]

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!