Forum Discussion

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

ralf_anton's avatar
ralf_anton
Icon for Resolver I rankResolver I
1 year ago

Hi,

 

here is my Solution:

 

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    HeaderHoch = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true]),
    Topics = Table.ColumnNames(Quelle),
    #"Sortierte Elemente" = List.Sort(Topics,Order.Ascending),
    TNSpalten = List.Skip(#"Sortierte Elemente",List.Count(#"Sortierte Elemente")/2),
    Jahre = {Number.From(Topics{0})..Number.From(Topics{List.Count(Topics)-2})},
    AnzK =List.Count(HeaderHoch[Kurs])-1,
    AnzJ = List.Count(Jahre)-1,
    
    Ausgabe = 
        List.Generate
        (
            () => [Kurs = 0, Jahr = 0],
            each [Jahr] <= AnzJ ,
            each
                [
                    Kurs = if [Kurs] < AnzK then  [Kurs] + 1 else  0, 
                    Jahr = if [Kurs] = AnzK then [Jahr] + 1 else [Jahr]
                ],
            each    [
                        Jahr = Jahre{[Jahr]} ,
                        Kurs = Record.Field(Quelle{[Kurs]+1}, Text.From(Jahre{[Jahr]})),
                        Teilnehmer = Record.Field(Quelle{[Kurs]+1}, TNSpalten{[Jahr]})
                        // Record.Field(Quelle{[Kurs]+1}, TNSpalten{[Kurs]})
                    ]

        ),
    NewTopics ={"Jahr","Kurs","Teilnehmer"},
    #"In Tabelle konvertiert" = Table.FromList(Ausgabe, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", NewTopics, NewTopics)
in
    #"Erweiterte Column1"