Forum Discussion

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    
        First   = Table.SelectColumns(Source,{"Naam", "employee1", "share employee1"}),
        Second  = Table.SelectColumns(Source,{"Naam", "employee2", "share employee2"}),
        names   = {"Naam", "employee", "share employee"},
        append  = Table.RenameColumns( First, List.Zip( {  Table.ColumnNames(First), names } ) ) &
                   Table.RenameColumns( Second, List.Zip( {  Table.ColumnNames(Second), names } ) ),
        SortIt = Table.Sort(append,{{"Naam", Order.Ascending}})
    in
        SortIt

    if I understood the logic correctly

  • let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Naam"}, "Attribut", "Wert"),
        #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index.1"},#"Added Index1",{"Index"},"Added Index1",JoinKind.LeftOuter),
        #"Expanded {0}" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Wert"}, {"Wert.1"}),
        #"Inserted Modulo" = Table.AddColumn(#"Expanded {0}", "Modulo", each Number.Mod([Index], 2), type number),
        #"Filtered Rows" = Table.SelectRows(#"Inserted Modulo", each ([Modulo] = 0)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribut", "Index", "Index.1", "Modulo"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Wert", "employee"}, {"Wert.1", "share employee"}})
    in
        #"Renamed Columns"

    The M code above returns the output in the green result table with basic transformations. The name of the blue table is "Tabelle1".

Resources