PowerBi Transpose - Table

Question

I have an excel file that needs to be transposed from multiple columns into table format, but am having trouble merging the rows in Power Query.

Sample Data

Expected Outcome

Thanks a lot for your help in advance.

Answer 1

Before loading into PowerQuery, insert a row in your excelsheet and concatenate the headers in the value columns, using a delimiter. You can use the TEXTJOIN function to do this if you use office365. The result looks something like this (I did not copy all your data):

Import this tabel into PowerQuery and perform the following steps:

1. Remove top 3 rows
2. Use first row as headers
3. Select the first 3 columns
4. Unpivot other columns (dropdown menu unpivot columns on Transform Tab)
5. Select [Attribute] column
6. Split column by delimiter (Semicolon)

The script looks like this.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Actual;jun;FY-2017", Int64.Type}, {"Actual;jul;FY-2017", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1", "Column2", "Column3"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}, {"Attribute.3", type text}})
in
    #"Changed Type2"

The result: