Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

How to Correlate/Connect Two Tables with a Matching Column in Power Query

See tables here. I am trying to create a merged table to create a new table/query in Power Query that does the following:

- All rows from table 1 have at least one row, even if its empty

- each row from table 2 exists in a separate row

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,
    Thans for Ahmedx reply.
    You can also try the following code

    let
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOyE9VitWJVjICcsITc3JSS8BcYyDXqSgzNS05sRiiwASsOrMIIm8K5HkkAtmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Description = _t]),
    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYvLDcAwCEN34ZwLGGh3Qdl/jfJJpB4s4edHBDGtiYD2CpK8K2DtjtNzZ2uiP9LASm9gY3iWsth9wHOAQublvYYgjf0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Table 1 ID" = _t, Lot = _t]),
    MergedTables = Table.NestedJoin(Source1, {"ID"}, Source2, {"Table 1 ID"}, "Table2", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "Table2", {"Lot"}),
    FinalTable = Table.ReplaceValue(ExpandedTable, null, "", Replacer.ReplaceValue, {"Lot"})
in
    FinalTable

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,
    Thans for Ahmedx reply.
    You can also try the following code

    let
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOyE9VitWJVjICcsITc3JSS8BcYyDXqSgzNS05sRiiwASsOrMIIm8K5HkkAtmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Description = _t]),
    Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYvLDcAwCEN34ZwLGGh3Qdl/jfJJpB4s4edHBDGtiYD2CpK8K2DtjtNzZ2uiP9LASm9gY3iWsth9wHOAQublvYYgjf0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Table 1 ID" = _t, Lot = _t]),
    MergedTables = Table.NestedJoin(Source1, {"ID"}, Source2, {"Table 1 ID"}, "Table2", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTables, "Table2", {"Lot"}),
    FinalTable = Table.ReplaceValue(ExpandedTable, null, "", Replacer.ReplaceValue, {"Lot"})
in
    FinalTable

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly