I need help with using Power Query

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 

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!

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"

Hi E12345,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to ralf_anton, KarinSzilagyi, tharunkumarRTK & Riny_vE for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

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

Hello E12345, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.