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
3sun_Jack
Regular Visitor

Connecting to a SharePoint List Office 365 error

Hi I am receiving the following error when I try and link a SharePoint site to Power BI

 

DataSource.Error: We couldn't parse OData response result.  Error: A null value was found for the property named 'Actionee1Id', which has the expected type 'Collection(Edm.Int32)[Nullable=False]'. The expected type 'Collection(Edm.Int32)[Nullable=False]' does not allow null values.
Details:
    DataSourceKind=SharePoint
    DataSourcePath=https://#######.sharepoint.com/BD/_api/Web/Lists(guid'#####-####-###-###-#####')/Items

 

I have seen this error before, but previously I could delete the column in question, unfortunately, this time I cannot

the field in question is a people field which is non mandatory

 

any help would be gratefully received

 

Kindest regards

 

Jack

 

1 ACCEPTED SOLUTION


I changed the ApiVersion from 15 to 14 in the Query Advanced Editor, and it works now 😃

let
    Source = SharePoint.Tables("https://###.sharepoint.com/teams/Team020/", [ApiVersion = 14]),
    ProjectList = Source{[Name="ProjectList"]}[Content]
in
    ProjectList

View solution in original post

37 REPLIES 37

Hi,

can you please give any more specific information on how we could do that? 🙂

Hi lelenko,

 

What is your problem about? Do you struggle with extraction data from SPO list, which has a many lookup and multi-lookup fields? Here "many" means more then threshold.

 

BR, Sergey

Can anyone give imformation on how we could do that?

I am getting this error:  DataSource.Error: OData: The feed's metadata document appears to be invalid.  I noticed other people are getting it.  Is there any other solution to this problem that might work?  I have been trying to work around the error all morning!

I got the same error too

I am getting this error:  DataSource.Error: OData: The feed's metadata document appears to be invalid.  I noticed other people are getting it.  Is there any other solution to this problem that might work?  I have been trying to work around the error all morning!

When I change ApiVersion to 14, SharePoint.Tables() function return nothing !

So I cannot use this workaround. Any other ideas ?

Jean-Pierre Riehl
MVP Data Platform

I found a workaround discussing with a SharePoint colleague. You can explicitly define fields you want to get and exclude the one that generate error.

 

add this parameter on OData query => $select=<fields comma-separated>

 

= OData.Feed("https://XXXXX.sharepoint.com/sites/pop/_api/Web/Lists(guid'abcd-efgh-ijkl-mnop-qrst')/Items?$select=...")

 

You can stay with version 15 API.

Jean-Pierre Riehl
MVP Data Platform

Hi @djeepy1!

 

Look at my script (automaticaly generated):

let
Origem = SharePoint.Tables("https://xxxxxxxxx.sharepoint.com/gestao/", [ApiVersion = 15]),
#"xxxxxx" = Origem{[Id="xxxxxx"]}[Items]
in
#"xxxxxx"

 

Where I have to put your command?

 

Tks.

@rgparisoto

 

You have to use OData.Feed function instead of SharePoint.Tables to reach your list.

I guess this should be :

 

let
  origem = OData.Feed("https://XXXXX.sharepoint.com/gestao/_api/Web/Li​sts(guid'yyyyyy')/Items?$select=zzzzzzz")
in
  origem

where

  • XXXX is your Tenant
  • yyyyy is your list guid (the one you have in your second line - [Id="xxxxxx"])
  • ZZZZ is a comma-separated list of fields you want to get

 

Notice I added feedback to my previous comment. I do not know if it applies to your error / context.

 

Jean-Pierre Riehl

Jean-Pierre Riehl
MVP Data Platform

Tell me please
Why does not this request?

 

let

    soursOData = Data.Feed("https://<app web url>/_api/web/Lists(guid'DEE5D045-C8AB-4878-B3F1-0D814EBF648E')/Items?$select=TextMonth,TextQuarter")

in 

  soursOData

 

Error OData.FeedError OData.Feed

P/S/

This code is working in the browser

You get a HTTP 404 error on /$metadata resource of your list on your SharePoint.
I'm not a SharePoint expert but maybe there's some specific setting on your list (or permission missing).
Jean-Pierre Riehl
MVP Data Platform

Thank you @djeepy1! I'll try this model.

Regards,

This error message will been given when a multiple user or multi choice field is empty. When all the fields are filled this error message will not been given

Thanks for the help. I went into the SahrePoint list and added and N/A choice then did quick edit to update all empty values to N/A and it worked!

 

Worth Noting:

We completely removed the column in the Power BI query and still got the error. We removed all null in the query editor and got the error as well. Only thing that worked was editing the column in SharePoint.

Any news on when this bug will be fixed? To be clear it affects both the Power BI desktop client as well as Excel (Power query).

Yes this work around works but we can hardly teach users how to do this workaround without getting a lot of issues. This really needs to be resolved as there is so much potential in getting nice reports and dashboards from data stored in SharePoint.

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