Connect to Sharepoint lists, some lists work, others give me a DataSource Error.

[RyanStewart]

Hello,

I am attempting to connect Power BI to one of my organization's lists on Sharepoint.  I am able to connect to one no problem, but the other one I want to connect to gives me the following error:

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (Input string was not in a correct format.)
Details:
DataSourceKind=SharePoint
DataSourcePath=https://hppi.sharepoint.com/sites/HirshWeb
SPRequestGuid=95b45c9f-9000-b000-c6aa-64e711531b33
Url=https://hppi.sharepoint.com/sites/HirshWeb/_api/Web/Lists(guid'29ad3ceb-dae7-4c12-90eb-190ff5488a00'...

I looked around for a solution online, saw some people suggested removing calculated columns from the list to fix the issue, but I have deleted all the calculated columns and am getting the same error.  I have tried clearing permissions of the data source and reconnecting a few times as well.  I am not sure what to do next.  Please help?

Thanks,

Ryan

[rubinboer]

hi there Ryan

I do not have a response directly to your problem. Have you considered having a flow updating an Excel file and have power BI connecting to Excel instead of a list?

Much fewer issues and it speeds up the process.

here is a link to it if you wish to investigate it. https://www.youtube.com/watch?v=ibqFGgkZ1Cs

rgds and best of luck

[TomMartens]

Hey @RyanStewart ,

please provide the code from Advanced Editor in Power Query.

Thanks,

Tom

[RyanStewart]

This code?

= Source{[Id="29ad3ceb-dae7-4c12-90eb-190ff5488a00"]}[Items]

[TomMartens]

Hey @RyanStewart ,

connecting to a SharepointOnline list I see something similar to this:

let 
Source = SharePoint.Tables("https://myfunnyname.sharepoint.com/sites/PowerBIIntegration/", [ApiVersion = 15]),
    #"1c293f5d-9d9b-49c5-8442-ff28c3d54c2c" = Source{[Id="1c293f5d-9d9b-49c5-8442-ff28c3d54c2c"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"1c293f5d-9d9b-49c5-8442-ff28c3d54c2c",{{"ID", "ID.1"}})

Regards,

Tom