Data from SharePoint list with Folders within the list?

[SevsBo]

We have a SharePoint list of open, rejected, and completed tickets:

I am trying to bring that into a Power BI report and I can get the data from the list quite easily using the SharePoint Online List connector. But that only gives me the items in the main part of that list, as well as two items called "Completed" and "Rejected", but not the actual tickets within them.

So, I can see Open tickets, but not Completed and Rejected, only the names of those folders appear in the list, I cannot see what is in those folders:

I have asked Copilot for help and it suggested using an older connector, V1.0 instead of 2.0, as that reveals additional data.

This worked, and I can now see all the tickets, but there are two problems:

- I'm not capturing all the fields, and the fields don't have the titles as seen on SharePoint, but use the underlying naming of folders

- I still can't easily filter on the content of only those folders, I have to use secondary indicators to determine if a ticket should be in those folders. For example, if it's status = Completed, then it will be in Complete folder. But that's not a reliable way of seeing the content of those two folders.

So am I missing something? Is there a better way to ingest the SharePoint list as a whole, and then drill down into the folders?

[v-menakakota]

Hi @SevsBo  ,

Thank you for reaching out to the Microsoft Community Forum.

Could you please let us know if the issue has been resolved?

If the issue still persists, could you share the Power Query M code from the Advanced Editor in Power Query? This will help us better understand how the SharePoint list data is being retrieved and identify why some fields are missing or appearing with internal names.

Best regards,
Community Support Team

[SevsBo]

@Lodha_Jaydeep  sure, here is the 2.0 connector query:

let
Source = SharePoint.Tables("https://COMPANY.sharepoint.com/sites/SITENAME", [Implementation = "2.0", ViewMode = "All"]),
#"Navigation 1" = Source{[Id = "ID"]}[Items],
#"Filtered Rows" = Table.SelectRows(#"Navigation 1", each ([Created Date] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Created Date", type date}}),
#"Expanded Owner" = Table.ExpandListColumn(#"Changed Type", "Owner"),
#"Expanded Owner1" = Table.ExpandRecordColumn(#"Expanded Owner", "Owner", {"title"}, {"Owner.title"}),
#"Expanded Reporter" = Table.ExpandListColumn(#"Expanded Owner1", "Reporter"),
#"Expanded Reporter1" = Table.ExpandRecordColumn(#"Expanded Reporter", "Reporter", {"title"}, {"Reporter.title"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Reporter1",{{"Resolved Date", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"ID", "Title", "Owner.title", "Status", "Created Date", "Reporter.title", "Customer Name", "Description", "Resolved Date", "Resolution", "Category", "Sub-Category", "Product Affected", "Historic Comments", "Raised By Historic Data", "Priority"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",null,"Unknown",Replacer.ReplaceValue,{"Owner.title"}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value", {{"Owner.title", "Error-Check"}})
in
#"Replaced Errors"

And this is the 1.0 connector that I am using to get all the rows of data, but it's missing some columns right from the start:

let
Source = SharePoint.Tables("https://COMPANY.sharepoint.com/sites/SITENAME", [Implementation = null, ApiVersion = 15]),
#"Navigation 1" = Source{[Id = "ID"]}[Items],
#"Expanded Folder" = Table.ExpandRecordColumn(#"Navigation 1", "Folder", {"ParentFolder"}, {"Folder.ParentFolder"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Folder",{"Id", "Title", "field_3", "field_4", "field_7", "field_8", "field_9", "field_11", "field_12", "field_13", "field_14", "field_10"}), --the columns that are missing are not here, we simply don't need these ones.
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"field_3", "Status"}, {"field_4", "Created Date"}, {"field_7", "Reporter"}, {"field_8", "Customer Name"}, {"field_9", "Description"}, {"field_11", "Category"}, {"field_12", "Sub-Category"}, {"field_13", "Product Affected"}, {"field_10", "Resolved Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Resolved Date", type date}, {"Created Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Title] <> "Completed" and [Title] <> "Rejected"))
in
#"Filtered Rows"

[Lodha_Jaydeep]

what is your expected O/P can you show? metadata is loading as the field1, fields2....fieldn columns, just want to make sure what is the expected O/P which should be return from the query!

[SevsBo]

I don't know what the metadata is for the missing fields, as I am not the Sharepoint owner, but the fields that are loaded (field1, filed2 etc) are not correct. I tried all but the required columns are not being captured by 1.0.

[v-menakakota]

Hi @SevsBo ,

Thank you for sharing both queries. Based on your testing, the two connector implementations are returning different results against the same SharePoint list. With Implementation="2.0", you're getting the expected display column names, but only the folder entries ("Completed" and "Rejected") are visible, while the items stored within those folders are not appearing in the returned data. With the legacy implementation (ApiVersion = 15), the additional ticket rows are returned, but several columns are exposed using internal SharePoint field names (field_x), and some of the fields you require are not present in the output.

Microsoft documents that Implementation 2.0 uses different APIs and is not backward compatible with the legacy implementation, so differences in returned metadata and behavior can occur between the two implementations.

To investigate further, could you share a few examples of the specific columns that are available in SharePoint and in the 2.0 output, but missing from the legacy output? That may help determine whether the difference is related to how each implementation exposes the list schema.

Documentation link - 

Power Query SharePoint Online list connector - Power Query | Microsoft Learn

Best regards,
Community Support Team

[SevsBo]

Apologies for the belated reply!

One field that is entirely missing is Created Date. It's visible in Sharepoint, and if mapped under field4, but this does not exist at all in V2 connector.

On the other hand, I can see data on Reporter in V2, but in V1 the best I can get is a number that is clearly meant for mapping, but don't know what it maps to or where.

An outlier is something like Created date, which is not visible on the Sharepoint as such, but exists in Metadata. It's visible in V1.0 connector, but not V2.0.

[v-menakakota]

Hi @SevsBo ,

Thank you for the additional details.

The examples you provided are helpful. The fact that Created Date is available through the legacy implementation but not visible in the Implementation="2.0" output, while the Reporter field is exposed differently between the two implementations, further demonstrates that the two connector implementations are returning different metadata and schemas for the same SharePoint list.

As documented by Microsoft, Implementation="2.0" uses different APIs and is not backward compatible with the legacy implementation, so differences in returned metadata and behavior can occur between the two implementations.

To help us investigate further, could you confirm whether the Reporter field is a Person column and whether the Created Date field is a standard SharePoint column or a custom column? If possible, please also share screenshots of the column settings from SharePoint.

Best regards,
Community Support Team

[v-menakakota]

Hi @SevsBo ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team

[Lodha_Jaydeep]

@SevsBo, can you share power query steps from the advanced aditor within the power query window?
It will help much better to fix the issue.