Forum Discussion

cooler1978's avatar
cooler1978
Regular Visitor
4 years ago
Solved

Table.Join - does it works with DirectQuery or not?

Hello.   I have sourse , table with around 1,2 mln rows. I store it in Direct Query mode, and i need to filter it by one column First variant is   let      Source= Sql.Database("192.168.1.33",...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    It's because SQL joining to a table outside of the db breaks query folding, which is essentially the case here.  Yoi could bring in the table 2x and filter to whatever and join that.  Also, are you sure you meant to use Left_Anti?If you want fast and folding though, try:

     

    Table.SelectRows(Table, each List.Contains(List.Buffer(List.Distinct({"00000000-0000-0000-0000-000000000000"})), [ID]))

     

    Which is "WHERE [Table].[ID] in ["00000000-0000-0000-0000-000000000000"]"

     

    --Nate

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

It's because SQL joining to a table outside of the db breaks query folding, which is essentially the case here.  Yoi could bring in the table 2x and filter to whatever and join that.  Also, are you sure you meant to use Left_Anti?If you want fast and folding though, try:

 

Table.SelectRows(Table, each List.Contains(List.Buffer(List.Distinct({"00000000-0000-0000-0000-000000000000"})), [ID]))

 

Which is "WHERE [Table].[ID] in ["00000000-0000-0000-0000-000000000000"]"

 

--Nate