SharePoint Online Lists 2.0 | How to get list items in subfolders?

[FrederikM]

Hello guys,

is it possible to access items in subfolders with the SharePoint Online Lists 2.0 connector? I dont get it working. 

With 1.0 it works but that one is terribly slow. I hoped that since 2.0 is not beta anymore this would work, but it doesn't.

Folders in ListItems in Subfolder of List

After Import i can only see the folders, but i dont have a possibility to expand on the items inside the folders.

[cwarshaw]

Did you ever figure this out? I'm in the exact same predicament. My default view shows all items without folders so I'm not understanding why it's just giving me folders... @mahoneypat would really appreciate your insight!

[mahoneypat]

You could try using a different ApiVersion in the function. See the documentation link below. You could also try making a view that has all the folders expanded, and then use the view parameter.

SharePoint.Tables - PowerQuery M | Microsoft Docs

If not, please see this article. I haven't confirmed but I expect using the REST API with the function in this article may work.

Get Data From SharePoint Lists … But Fast – Hoosier BI

Pat

[FrederikM]

---

@mahoneypat wrote:

You could try using a different ApiVersion in the function. See the documentation link below. You could also try making a view that has all the folders expanded, and then use the view parameter.

SharePoint.Tables - PowerQuery M | Microsoft Docs

If not, please see this article. I haven't confirmed but I expect using the REST API with the function in this article may work.

Get Data From SharePoint Lists … But Fast – Hoosier BI

Pat

---

let
    Quelle = SharePoint.Tables("https://test.sharepoint.com/sites/test", [Implementation="2.0", ViewMode="All"])
in
    Quelle

When you go to Implementation 2.0 there are no API-Versions anymore, and ViewMode can only be All or Standard. I've set the StandardView that has all the folders expanded, but doesnt work anyway, folders are still visible in the query.

I'm currently using the REST API as a workaround since some months as a part solution for that problem. BUT, that works only for PowerBI Desktop, when i upload the Dashboard to the Power BI Service, the Dataset cannot be refreshed.

"This dataset includes a dynamic data source. Since dynamic data sources are not updated in the Power BI service, this dataset is not updated. For more information, visit https://aka.ms/dynamic-data-sources."

Is there a solution to connect a SharePoint List to the Power BI Service so i can use an automatic refresh of the dataset?

---

[FrederikM]

Hello,

i'm also using the REST Version (workaround) since over a year shown in the YouTube Video: Get SharePoint List Data with Power BI ... Fast - YouTube

And all the users that are using the Dashboard have to use PowerBI Desktop as well as Managers and CEOs.

But as @cwarshaw wrote, when you upload the Dashboard to the Power BI Service, the Dataset cannot be refreshed.

@mahoneypat : There are two main issues:

1. Using API Version 2.0 = If StandardView has all the folders expanded, but it doesnt work anyway.

2. When Using REST Version when you upload the Dashboard to the Power BI Service, the Dataset cannot be refreshed due to dynamic datasource problem.

That shoud really be added as a feature for future versions of PowerBI

[mahoneypat · Accepted solution]

@FrederikM FYI that I updated that blog recently to enable service refresh. 

Updated – Get SharePoint List Data … Fast – Hoosier BI

Pat

[cwarshaw]

Looks promising, I'll check it out! Any idea about getting subfolder content with 2.0? Would be much simpler 😉

[FrederikM]

Oh great thanks for your Update 😊