Forum Discussion

Shivam_Rai's avatar
Shivam_Rai
Copper Contributor
Sep 10, 2023
Solved

NEED HELP REGARDING MERGING TWO TABLES INTO ONE

Hi! I need some help. I have two tables in excel, Table-1 with a list of 785 rank entries, and Table-2 with 433 rank entries with other associated data. I need to merge data of two tables into one w...
excel
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 10, 2023

    Shivam_Rai 

    If something like this

    query both sources, rename in first ID=>ID1, second ID=>ID2 (rank in your case) and merge as new table with FullOuter option. Script is like

    let
    Source = Table.NestedJoin(
        Table1, {"ID1"},
        Table2, {"ID2"},
        "Table2",
        JoinKind.FullOuter),
    names1 = List.RemoveLastN( Table.ColumnNames( Source ), 1),
    names2 = Table.ColumnNames( Source[Table2]{0} ),
    expandMerged = Table.ExpandTableColumn(
        Source,
        "Table2",
        names2, names2),
    addID = Table.AddColumn(
        expandMerged,
        "ID",
        each List.RemoveNulls( {[ID1]} & {[ID2]} ){0},
        Int64.Type),
    keepColumns = Table.SelectColumns(
       addID,
       {"ID"} & List.Skip(names1) & List.Skip(names2) ),
    sortByID = Table.Sort(keepColumns,{{"ID", Order.Ascending}})
in
    sortByID
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 10, 2023

Shivam_Rai 

Easier with Power Query or you are looking for formula solution?

  • Shivam_Rai's avatar
    Shivam_Rai
    Copper Contributor
    Sep 10, 2023
    Thanks for your response. I would prefer Power Query, but I can try by any method possible.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 10, 2023

      Shivam_Rai 

      If something like this

      query both sources, rename in first ID=>ID1, second ID=>ID2 (rank in your case) and merge as new table with FullOuter option. Script is like

      let
    Source = Table.NestedJoin(
        Table1, {"ID1"},
        Table2, {"ID2"},
        "Table2",
        JoinKind.FullOuter),
    names1 = List.RemoveLastN( Table.ColumnNames( Source ), 1),
    names2 = Table.ColumnNames( Source[Table2]{0} ),
    expandMerged = Table.ExpandTableColumn(
        Source,
        "Table2",
        names2, names2),
    addID = Table.AddColumn(
        expandMerged,
        "ID",
        each List.RemoveNulls( {[ID1]} & {[ID2]} ){0},
        Int64.Type),
    keepColumns = Table.SelectColumns(
       addID,
       {"ID"} & List.Skip(names1) & List.Skip(names2) ),
    sortByID = Table.Sort(keepColumns,{{"ID", Order.Ascending}})
in
    sortByID

Resources