Loading a SharePoint list with "Cascaded Cross Site Lookup" fields

[Matthew_Berry]

Hi All,

I'm trying to load a list from SharePoint into PowerBI to manipulate and use as a data source.  I've loaded other lists but get an error when trying to load a list containing "Cascaded Cross Site lookup" fields.

Simple questions: Are lists with this type of field supported by PowerBI?  As far as I can tell this is the contributing factor causing the error, but would be good to canvas opinion and get an "official" statemnet.

Thanks in advance

[Anonymous]

Hi, @Matthew_Berry 

According to your description, you want to know if List type fields are supported for importing into Power BI and in SharePoint using the "Export to Excel" feature shows the URL of the query definition and the data content instead of the data content. Power BI does support importing List type fields, When you connect to the data source, you can use Table.FromColumns, Table.FromList, List.Transform in Power Query to accomplish your purpose.

Related links:

https://social.technet.microsoft.com/Forums/en-US/bf633836-b8cb-412c-a02b-c1199ace84d9/power-query-e...

Solved: Expand column containing list of records - Microsoft Fabric Community

Official Documentation: Table.FromList - PowerQuery M | Microsoft Learn

For the second question, in SharePoint or Microsoft Lists applications, you can export the list items displayed in the current view to Excel so that you can work with the data in the workbook. When you export to Excel, the resulting file is an .iqy file, which is a Web query file. This file contains the information needed to query the SharePoint list, including the URL and query definition. If you want to see the actual data in Excel instead of the query definition, you need to open the .iqy file. When you open this file, Excel executes the query and displays the results in a new worksheet. When using the Export to Excel feature in SharePoint, when exporting a job, the Cascading Cross Site Lookup field is a List, but instead of displaying the data content, it displays the URL query definition and the data content.

Related links:

https://support.microsoft.com/en-us/office/export-to-excel-from-sharepoint-or-lists-bfb2ea48-6118-4f...

https://answers.microsoft.com/en-us/msoffice/forum/all/exporting-share-point-list-to-excel-and-showi...

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Matthew_Berry]

Thanks for taking the time to reply. I know very well that normal SP Lists can be imported into Power BI and have done so many times, including fields that are related lists with single or multiple values.

What I was looking for was specific information on types of fileds supported, notably "Cascaded Cross Site Lookup."  All my other lists work import with no issues, as soon as I use a "Cascaded Cross Site Lookup" the import fails, remove that field in a list and it imports again.

Thats a strong indication its not supported.  I looking for independent verification of my findings, either in MS Documentations or via somebody elses experience.

[Matthew_Berry]

I've also noted that within SharePoint when using the feature "Export to Excel" whilst the export works, the fields that are "Cascaded Cross Site lookup" don't show the data content but a URL query definition plus the data content.