Table.NestedJoin vs List

Power Query M Code Showcase
1

Ok,
I am sure I have seen this somewhere bu Google is failing to find the answer I am looking for.

I have a Query for the date dimension (contains many columns including calendar month and fiscal month)

I have a query for Inventory (this conyains calendar month)

I need to add fiscal month to Inventory and have done this through Table.NestedJoin - Join kind left outer and then expanded the resulkts to show just fiscal month. This seems slow. Calendar onlt has 30 rows so can I use a list or table in buffer to speed this up?

Any example hratefully received
Thanks
E

2

Hi @ells,

Use Projection in combination with Buffering on the Calendar table (see: Table2Optimized). There are two Join functions you can compare both illustrated in the example below.

let
    Table1 = Table.FromRecords({
            [CustomerToCall = 1],
            [CustomerToCall = 3]
        }),
    Table2 = Table.FromRecords({
            [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
            [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
            [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
            [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
        }),
    Table2Optimized = Table.Buffer(
        Table2[[CustomerID], [Name]]
    ),
    Join1 = Table.NestedJoin( 
        Table1, {"CustomerToCall"},
        Table2Optimized, {"CustomerID"},
        "CustomerDetails"
    ),
    Join2 = Table.Join(
        Table1, {"CustomerToCall"},
        Table2Optimized, {"CustomerID"},
        JoinKind.LeftOuter
    )
in 
    Join2

.
Learn more about Projection and Selection in this video.

.
Discover more about optimizing merges in this series by Chris Webb:
Optimising The Performance Of Power Query Merges In Power BI, Part 5: Cross Joins

I hope this is helpful

3

Awesome, Thanks
E