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

[jmaur769]

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

[Anonymous · Accepted solution]

Hi @jmaur769 ,
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

[Omid_Motamedise]

See this video including all you need to know about merging

https://www.youtube.com/watch?v=Nh0Ch-fQc_k&t=35s

[Ahmedx]

pls try