Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Seward12533
Solution Sage
Solution Sage

Using query string from IQY file from Sharepoint "export to excel" as PowerQuery source in PowerBI

Hi, I'm trying to use a Sharepoint View as a source.

 

I do this often with the Sharepoint List connector but there is one list I'm using is very large and complex and it can take a long time just to load the list into the Query Editor and 5-10 Minutes to actually excecute the query.  Data I need is only a few hundred rows and there is an existing view that has the data I need. 

 

As a workaround somone suggested I use the "Export to Excel" function of SP to generate an IQY query.  For a site I have Admin Rights on it works flawlessly.  Unfortunatly the list I want is in another collection and I have permissions issues. 

 

This is what the URL looks like from the IQY file URL  

 

https://.../_vti_bin/owssvr.dll?XMLDATA=1&List={3D259A93-37AE-48BF-986C-53E0C64522DB}&View={29EE8B86-DE90-4285-9159-2A9F1F0ADE2D}&RowLimit=0&RootFolder=%2fsites%2fccg%2fSO%2fLists%2fRecipientLookup 

 

 

When I try this from PowerQuery I get permissions issues but from Excel it will refresh without issues. 

 

Note - I woudl just use the excel as a source we have onedrive disabled in our environment so its difficult to publish an excel source that will be refershed in the service. 

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

Please see this video on how to get data from a SharePoint list 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


View solution in original post

@mahoneypat Thanks, I'll check it out. Unfortuneatly the list is not SP Online but ratehr a SP2019 on Prem so there is no V2..  I'll try to get the REST solution working!!

View solution in original post

3 REPLIES 3
mahoneypat
Employee
Employee

Please see this video on how to get data from a SharePoint list 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


@mahoneypat the REST API Function you provided is fabulous.  Thanks!! I may also be able to repurpose it for a JSON query I was trying to use and was stuggling to handle the 5000 record limit!!

@mahoneypat Thanks, I'll check it out. Unfortuneatly the list is not SP Online but ratehr a SP2019 on Prem so there is no V2..  I'll try to get the REST solution working!!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors