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 without losing non-mutual entries in the Table-1.

 (First Table in the image is Table-2 and second one with one column is Table-1).

There are some extra entries in table 1 which are not present on table two. I want to merge them by keeping non mutual entries entact and blank.

How to do so? Any suggestion/help would be appreciated.

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
    • 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
        MVP
        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