Forum Discussion

Resolver II

1 year ago

Solved

I need help with using Power Query

Hi, I have a table which I need to make usable for Power BI. The table is in Excel, and it has double headers. The top column is Year, and underneath each year there are two more columns - Course...

Need Help

KarinSzilagyi
1 year ago
Hi E12345, you need a combination of transpose/pivot/filldown to achieve what you are trying to do

Step 1: Your starting point should look like this ideally => years not in the headers, so we can easily use Transpose + Fill Down in the next steps:
Step 2: Click on Column1 and apply Transform > Transpose. This will give you a new Column1 with one row for each year + a blank row below it. Use Transform > Replace Value if the empty rows (2, 4, 6 and 😎 are not null but blank (= look empty)

Step 3: Select Column1 and apply Transform > Fill > Down to fill the year-values into your null-rows:

Step 4: Select Column1 + Column2 and apply Transform > Unpivot Columns > Unpivot other Columns:

Step 5: Now Select Column2 (the one with the the values Course and Completions) and apply Transform > Pivot Column. Set the "Values Column" to "Value" (the one with Course A, 34 etc) and open the Advanced options to select "Don't Aggregate"

Step 6: Rename the Columns as needed, delete the extra column "Attribute" and adjust the datatypes as needed:

I hope that helps!

Riny_vE

Helper I

1 year ago

Here you go. All through the UI. No manual M coding needed.

let
    Source = Excel.CurrentWorkbook(){[Name="myData"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filled Down", {{"Column1", type text}}, "en-001"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Header"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute.2]), "Attribute.2", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute.1", "Year"}})
in
    #"Renamed Columns"

This will take the grey data (named range "myData") to the green table.