cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cooler1978
Regular Visitor

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", "bi"),
     Table = Source{[Schema="dbo",Item="Priceset"]}[Data],
     Filter = Table.Join (Table, "ID", Table.RenameColumns(Table.SelectColumns(Useditems,{"ID"}), {{"ID","UsedID"}}), "UsedID", JoinKind.Inner),
in 

     Filter

Works fine - i got in Power BI direct query mode table, so everything is ok.

The same source, same table i need to filter by one column

let
   Source= Sql.Database("192.168.1.33", "bi"),

   Table = Source{[Schema="dbo",Item="Priceset"]}[Data],
   Filter = Table.Join (Table, "IDХарактеристики", Table.FromRecords ({[ NEWID = "00000000-0000-0000-0000-000000000000"]}) "NEWID", JoinKind.LeftAnti),
in
   Filter

 

I got Error - that this step results in a query that is not supported in directquery mode, i see no difference between first query and second, so why it happens?

Why i am using Join instead if Table.SelectRows?  my idea was based on information that Join works much faster than Table.SelectRows - when your table goes 20mln and over, it realy make sense to use join - my small expirience shows that it's true, it's realy works much faster.

So in my case Join used as filter.

1 ACCEPTED SOLUTION
watkinnc
Super User
Super User

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

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

3 REPLIES 3
cooler1978
Regular Visitor

 

Many thanks to all, everything solved - it's absolutely true, that the problem is that both table must come from the same SQL base. In this case everything works excelent.

Many thanks again to all!

v-yingjl
Community Support
Community Support

Hi @cooler1978 ,

It has related to query folding in Power Query, please note: Transformations that prevent folding 

  • Merging queries based on different sources.

  • Appending (union-ing) queries based on different sources.

  • Adding custom columns with complex logic. Complex logic implies the use of M functions that have no equivalent functions in the data source. For example, the following expressions format the OrderDate column value (to return a text value).

  • Adding index columns.

  • Changing a column data type.

 

In your second query, you have used 'Table.FromRecords' which would be recognized as another source so Power Query would remind the previous error.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

watkinnc
Super User
Super User

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

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors