cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Prom
New Member

Getting Query Folding to work with a SharePoint Online List

Hi,

 

I've recently started connecting to SharePoint Online Lists using Power Query and am hoping to get query folding working.

 

When I look under 'Sources that support folding' in Microsoft's query folding documentation, I can see that it mentions that SharePoint lists can be folded:

Prom_0-1635399936544.png

 

However in my testing I cannot seem to get it to work. Has anyone had any luck, or knows of any other documentation about query folding for a SharePoint Online list?

 

Added info:

  • I'm using SharePoint Online
  • I've tried connecting using all of the standard SharePoint connectors as well as OData methods described here and here.
  • To check whether query folding is working I've been placing a single column filter on the table I get from the list and right clicking to see if 'View Native Query' is available as described here.

 

(note: there's another post on this question, but it's quite old, so thought I'd create a new one in the hope that someone has an answer)

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @Prom ,

As far as I know, web sources like SharePoint Online etc. could not use 'View Native Query' to check whether the folding happens directly.

The data source that can view native query must be folded, and the data source that can’t view native query is not necessarily not folded.

 

Share Point Online sends some ODATA requests essentially that you can try to use Fiddler to catch the requests when you connect it directly and use ODATA to connect with the same operations like remove columns which features support query folding to compare whether the requests are the same.

 

Query folding means that every time all the data will be taken to Power BI for processing, so the request sent out is to take all the data

 

For folding, such as deleting some columns, then you can observe that the request sent by Power BI only contains those columns that I need.

 

Hopes it could help.

 

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.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @Prom ,

As far as I know, web sources like SharePoint Online etc. could not use 'View Native Query' to check whether the folding happens directly.

The data source that can view native query must be folded, and the data source that can’t view native query is not necessarily not folded.

 

Share Point Online sends some ODATA requests essentially that you can try to use Fiddler to catch the requests when you connect it directly and use ODATA to connect with the same operations like remove columns which features support query folding to compare whether the requests are the same.

 

Query folding means that every time all the data will be taken to Power BI for processing, so the request sent out is to take all the data

 

For folding, such as deleting some columns, then you can observe that the request sent by Power BI only contains those columns that I need.

 

Hopes it could help.

 

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.

View solution in original post

mahoneypat
Super User
Super User

Please see this video.  It doesn't leverage folding, but it much faster.

Get SharePoint List Data with Power BI ... Fast - YouTube

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hi Pat,

 

Thanks for that, but I was really looking for query folding. 

 

It's not so much the speed of the query that I'm looking for, but more the ability to have the server pick up the load for some subsequent transformations.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors