I need help with using Power Query

[E12345]

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 and Completions. Can you help me make this into a table that lets me use the data inside Power BI?  need this data converted into three columns: Year, Course and Completions. 

Below is the data Sample - Thank You! Is it possible provide a solution with GUI steps only (not M code or Advanced Editor)? 
What if Another year is added (2026) - can the ETL be dynamic enough to account for another year?

2025	2024	2023	2022
Course	Completions	Course	Completions	Course	Completions	Course	Completions
Course A	34	Course C	126	Course A	56	Course K	123
Course B	45	Course B	89	Course B	55	Course B	234
Course C	56	Course F	77	Course C	77	Course C	34
Course D	67	Course D	100	Course J	48	Course J	45

Original Data Looks like this in Excel. 

[ralf_anton]

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"

[KarinSzilagyi]

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! 

[tharunkumarRTK]

@E12345 

Input data

Output results 

Please find the code

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pY69DsIwDIRfpfLcIXXi/owliAEeIcrYAQmaipb3x+6QvxXJw33n09nOASokaIGHlYlKR4WifOvAhu9nX5hseG+v5XiGdT/pbzu1NzOvtInBxrLssE+GBCjjxxnQeceFLUMpIjxOJVO1Rz6aVdjqyo3lMJRv1Vw2XNnps4Rwp1Qy7vLlWDGB9z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
  #"Kept top rows" = Table.FirstN(Source, 2),
  #"Transposed table" = Table.Transpose(#"Kept top rows"),
  #"Replaced value" = Table.ReplaceValue(#"Transposed table", "", null, Replacer.ReplaceValue, {"Column1"}),
  #"Filled down" = List.Transform(Table.Split(Table.FillDown(#"Replaced value", {"Column1"}),2), each List.Transform(Table.ToColumns(_), each  List.Distinct(_) )),
  #"Removed top rows" = Table.Skip(Source, 2),
  ColumsnAndYear = List.Split(Table.ToColumns(#"Removed top rows"),2),
  #"Converted to table" = Table.FromList(ColumsnAndYear, Splitter.SplitByNothing()),
  #"Added index" = Table.AddIndexColumn(#"Converted to table", "Index", 0, 1, Int64.Type),
  #"Added custom" = Table.AddColumn(#"Added index", "Custom", each let 
columns = #"Filled down"{[Index]}{1},
year = #"Filled down"{[Index]}{0}{0},
result = Table.AddColumn(Table.FromColumns([Column1],columns),  "Year" ,each year)
in 
result),
  Custom = Table.Combine(#"Added custom"[Custom])
in
  Custom

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Riny_vE]

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.

[E12345]

I cannot recreate it... My table looks weird when I merge. I will try again later.