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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ambtb
Frequent Visitor

Power BI with Sharepoint issues

Hello,

I need to get some Sharepoint Lists data to Power BI.

 

So far I'm having the following issues, using the latest version of Power BI for Desktop.

 

Using the Sharepoint List DataSource, when the sharepoint list is empty it returns one table without any columns and any schema.

 

Using OData to connect to sharepoint ListaData.svc, I receive the table columns when the list is empty but some columns are not returned at all, for exemple columns with more complex data types (that translate to List and Record types using the Sharepoint List DataSource).

 

Is there any workaround or a way to get the correct sharepoint data?

 

Best regards,

António Baptista

9 REPLIES 9
v-yuezhe-msft
Employee
Employee

@ambtb,

I am not able to reproduce your issue in the latest version of Power BI Desktop(2.48.4792.481 64-bit (July 2017)). Do you enter the root URL of your SharePoint online site when connecting to the online list using the connector as shown in the first screenshot? And do you use Microsoft account to connect to the online list as shown in the second screenshot?
1.PNG2.PNG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

I'm using the same Power BI Desktop version, I'm connecting to the root URL of sharepoint site, and using a Microsoft Account.

 

 

Hera are the screenshots of what I'm getting:

 

screen1.png

 

screen2.png

 

Best Regards,

 

@ambtb,

It seems that you don't choose SharePoint list source, ensure that you choose relative SharePoint list as shown in the screenshot below. In my scenario, appdata is also empty.

1.PNG

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

thank you for the answer.

 

I don´t know if I have explained well my issue.

The problem is exactly that the metadata from the list is not being returned if the list is empty, and with that behaviour is difficult to shape the data (for example, I can't remove columns from the source, or change type, or chage name, or expand because if the list becames empty it gives an error).

 

I'm doing exactly as you, selecting a list present in the sharepoint site.

 

screen3.pngscreen5.pngscreen4.png

 

@ambtb,

This is expected behavior that empty SharePoint list show as an empty table in Power BI Desktop.

However, when you connect to a non-empty SharePoint list in Power BI Desktop and implement some modeling on the imported query, if you delete all items in the SharePoint list, then refresh data in Power BI Desktop you will get error. In this case, open query editor in Power BI Desktop, click on your query , and you will see APPLIED STEPS in Query Setting panel, delete the steps(Renamed Columns ,Change type and so on), the error should go away.


Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, thank you for your answer.

 

Unfortunetly that expected behaviour can make the sharepoint list datasource difficult to use.

 

I cannot give a report to users based on a sharepoint list that can become empty because in that particular case it will give an error (because of the shaping of the datasource, remove columns, rename, etc) and it is not supposed that the users edit the data model.

 

Best regards,

@ambtb,

When the list becomes empty, please delete the steps to fix this error or remove the list from your PBIX file and re-distribute the report to your users.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ambtb
Frequent Visitor

Hello,

I'm getting some sharepoint list data to PowerBI Desktop (lates version) to do a report, but I'm finding some limitations and so far no workaround.

 

When using the sharepoint online list data source, when the sharepoint list is empty, an empty table is returned, with no column metadata.

 

When using the odata data source, with listdata.svc, the column metadata is returned when the list is empty, but complex data type columns are not returned (for example, columns that evaluate to List and Record types when using the list datasource).

 

Is there any work around for getting the correct data from sharepoint?

 

Thanks,

ambtb
Frequent Visitor

Hello,

I need to get some list data from Sharepoint online for a report.

 

So far I'm having the following issues:

 

Using the sharepoint online list datasource, when the list is empty I receive an empty table from the datasource, without any columns or any metadata information.

 

Using OData with sharepoint Listdata.svc, I receive column metadata when the list is empty, but some columns with more complex data types I don't receive at all (for example, columns that translate to List or Record types while using Sharepoint List Data Source).

 

Is there any work around or a way to get the correct sharepoint data?

 

Thank you,

António Baptista

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors