Forum Discussion

E12345's avatar
E12345
Icon for Resolver II rankResolver II
11 months 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's avatar
    KarinSzilagyi
    11 months 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! 

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
11 months ago

Hi E12345, another solutions:

Result:

 

v1

let
    Source = Excel.CurrentWorkbook(),
    Range = Source{[Name="Range"]}[Content],
    Years = List.RemoveNulls(Record.ToList(Range{0})),
    Transformed = Table.Combine(List.Transform(Table.ToRows(Table.Skip(Range, 2)), each Table.FromRows(List.Transform(List.Zip({ Years, List.Split(_, 2) }), (x)=> {x{0}} & x{1}), type table[Year=Int64.Type, Course=text, Competitors=number])))
in
    Transformed

 

v2

let
    Source = Excel.CurrentWorkbook(),
    Range = Source{[Name="Range"]}[Content],
    Transposed = Table.FillDown(Table.FromColumns(Table.ToRows(Range)), {"Column1"}),
    RemovedColumns = Table.RemoveColumns(Transposed,{"Column2"}),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(RemovedColumns, {"Column1"}, "Attribute", "Competitions"),
    Ad_Course = Table.FillDown(Table.AddColumn(UnpivotedOtherColumns, "Course", each if Value.FromText([Competitions]) is text then [Competitions] else null, type text), {"Course"}),
    FilteredRows = Table.SelectRows(Ad_Course, each [Course] <> [Competitions] ),
    RemovedOtherColumns = Table.SelectColumns(FilteredRows,{"Column1", "Course", "Competitions"})
in
    RemovedOtherColumns