Connecting to a SharePoint List Office 365 error

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

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.

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

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).

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=Title,ID")

You can stay with version 15 API.

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.Feed

P/S/

This code is working in the browser

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 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 ?

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.

Thank you djeepy1! I'll try this model.

Regards,

I got the same error too

Can anyone give imformation on how we could do that?

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

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.