Merging two queries in Power Query: find "M00123" using lookup value of "123"

[danhendo888]

I have a table in Sheet1 (query1) which has vendor numbers and invoice numbers
Sheet2 (query2) is the data table and has vendor numbers, invoice numbers, amount, paid dates

I want to load both as a query and merge them
I would do 'group by' for the Sheet1 table using the first two columns to show unique values
For Sheet2, I would 'group by' vendor, invoice number, amount

Suppose I want to lookup 123 in query1 and find invoice M00123 in query2
How do I merge the queries and have M00123's invoice amount and paid date show up for 123?

https://1drv.ms/x/s!AvjBsEPEq12ngTcWgQnwXMZmJm_2?e=HQJC3s

 

[sandy666]

maybe

[SIZE=1]let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Merge = Table.TransformColumnTypes(Table.ExpandTableColumn(Table.NestedJoin(Source1,{"Vendor"},Source2,{"Vendor"},"Source2",JoinKind.LeftOuter), "Source2", {"Amount", "Paid Date"}, {"Amount", "Paid Date"}),{{"Paid Date", type date}})
in
    Merge[/SIZE]

btw. your description does not match the example

 

[danhendo888]

Have updated workbook in the link with Sandy's code

In the Merge query, the last Applied Step has the below code:

= Table.TransformColumnTypes(Table.ExpandTableColumn(Table.NestedJoin(Source,{"Vendor"},Source2,{"Vendor"},"Source2",JoinKind.LeftOuter), "Source2", {"Amount", "Paid Date"}, {"Amount", "Paid Date"}),{{"Paid Date", type date}})

Could someone please help me understand the functions Table.ExpandTableColumn and Table.NestedJoin, and how it manages to display the records of invoice M00123 from Query2 using lookup value of 123 from Query1

 

[sandy666]

the result of the M-code is not what you want?
I see result and your output - this is the same except date format.

 

[danhendo888]

It is what I want but I'm trying to understand the functions. You combined several functions into one applied step. That's great, but as a beginner, kinda hard to learn from it and apply it/practise on other examples.
Anyway, thank you for your help, I appreciate it.

 

[sandy666]

sure, now is better?

[SIZE=1]let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Merge = Table.NestedJoin(Source1,{"Vendor"},Source2,{"Vendor"},"Source2",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Merge, "Source2", {"Amount", "Paid Date"}, {"Amount", "Paid Date"}),
    Type = Table.TransformColumnTypes(Expand,{{"Paid Date", type date}})
in
    Type[/SIZE]