Import sharepoint list like excel

[hjaf]

The sharepoint excel export function generates a .jqy file that I can use with excel, is there a way to use the same method for powerbi directly? I've been looking into OLE DB, which seems to be the way excel does it. There are so many different ways and the documentation on this is somewhat convoluted, perhaps someone here has already done this or know of a guide 🙂 

Sharepoint list queries become insanely slow on large/complex lists, I've been forced to export the sharepoint list to excel first and then import data to my report from the excel table. It is a really ugly workaround, but fetching 15k + rows from the same sharepoint list in the standard way takes several hours while the excel query takes 15-20 seconds to get the information i need. The primary problem with this workaround is that this is not going to work for scheduled dataset updates, because I still have to manually update an excel file.

In other words, what I want is to be able to make use of the sharepoint list views, so that I don't have to do that same work with powerquery again. I believe the reason for the standard sharepoint list query being slow is due to a need for expanding records or reference columns like user claims, lookup/choice etc.

[amitchandak]

Not sure I got it. But power bi service allows you to download data for analyzing on excel

https://docs.microsoft.com/en-us/power-bi/service-analyze-in-excel

[hjaf]

No, sorry if i was unclear. 

I want to analyze data from a sharepoint list, but I want to get the data the same way I would get data from a sharepoint list into excel. When navigating to a listview in sharepoint you get the option to export to excel. 

[v-yuta-msft]

@hjaf ,

You can also export underlying data or summarize data from power bi service. You can refer to doc below:

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hjaf]

Again, I'm sorry for not explaining it clearly.

I want to create a report that use data stored in a sharepoint list, I do not want to use Excel at all.

SharePoint -> Power BI

Excel is only mentioned because of its method of getting data from sharepoint.

[AilleryO]

Hi @hjaf ,

So I think your solution relies on connecting directly to your SharePoint List.

Create a new data source and select :

SharePoint List or SharePoint 365 List

(depending on wether you use SharePoint online 365 or SharePoint server).

Then enter the URL of the home page of your SharePoint site, and select your list in the drop down menu.

More details here :

https://docs.microsoft.com/en-us/power-bi/desktop-sharepoint-online-list

Have a nice day and do not forget to Accept as solution if this is the case 😉

[hjaf]

No, as I tried to explain with the initial post, this is what I have to work around. This is a method I'd normally use when getting data from sharepoint. But because the size and complexity of the list is such as it is, this method is going to take hours (if not days).

What I want to do is use the same method excel uses when importing sharepoint list with the ".iqy" file that sharepoint provides for an export to excel (see screenshot from earlier reply). This method seems to import a 2D table that a sharepoint list view generates, there is no need to expand records from the table and is extremely fast in comparison. 

[gpfox]

Please for heaven's sake somebody help us with this. I agree it is just unfathomably slow to bring list items from a SharePoint Online List items into an Excel spreadsheet or into Power BI using Power Query. It's really embarrassing that these Microsoft products and they integrate so poorly. Agreed, it would be so much faster just to be able to automate the export of large or multiple SharePoint lists using the .iqy export from SharePoint List to Excel method. Please Microsoft the entire utility of SharePoint is severely weakened by it's terrible connectivity with Power Query

[mahoneypat]

Please see this post on how to get SharePoint List data ... but fast.

https://community.powerbi.com/t5/Desktop/SharePoint-list-query-alternative-or-optimization/m-p/10930...

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat