Dates between merge join / Expand columns performance issue

Hello edhans ,

And thank you for your answer!

Indeed, I don't think that this second step of my query is the issue. 

If I just do step 1:

- merge the two tables based on the client ID

#"Merge" = Table.NestedJoin(#"Table A", {"client ID"}, #"Table B", {"client ID"}, "new_column", JoinKind.LeftOuter)

and step 2:

- add a custom column to do the row selection based on the date range

#"Date range selection" = Table.AddColumn(#"Merge", "custom", each let mydate= [date] in Table.SelectRows([new_column],each [start date] <= mydate and [end date] >= mydate))

my query gives a result in less than 2 minutes.

This is really when I add step 3:

- then expand my new custom column

#"Result" = Table.ExpandTableColumn(#"Date range selection", "custom", {"start date", "end date", "category"}, {"start date", "end date", "category"})

that the query never ends, even after two hours of waiting. 

I did a new test this morning and reduced table A to 5 rows instead of 30,000 rows and it gave me a result in 5 minutes. But that's what I feared, Power Query is reloading table B for each line of table A on step 3. Table B is 38 Mo and I saw Power Query loading 38 x 5 = 190 Mo of data. So if I want to do this with my complete table A of 30,000 rows, I would need 30,000 x 38 = 1,140 Go loaded and 500 hours! Is there any way to say to Power Query to keep table B in the RAM when it is loaded instead of re-loading it for each line of table A?

Thanks!

Vivien