Power Query breaks my Odata Query

[Julian-K]

Hi!

So I'm doing Odata-Request on a Sharepoint list like this:

= OData.Feed("https://XXX.sharepoint.com/XXX/_api/lists/getbytitle('XXX')/items?$select=some,columns,Leasing_x002d_Status,lots,more&$filter=Logistics_x002d_Status eq 'reserved'", null, [Implementation="2.0"])

So I got some columns names with dashes in them and they need to be escaped.

Power Query successfully loads the data and shows me a preview, but somewhere steps further down (I assume when it's no longer possible to fold my further operations into the query) or when I force Power Query to execute the Odata query like so

= Table.Buffer(Odata.Feed("like above..."))

I get an error 400: 'Leasing_x002D_Status' does not exist.

Details:
    DataSourceKind=OData
DataSourcePath=https://XXX.sharepoint.com/XXX/_api/lists/getbytitle('XXX')/items
    SPRequestGuid=XXX
Url=https://XXX.sharepoint.com/XXX/_api/lists/getbytitle('XXX')/items?$filter=Logistics_x002d_Status eq 'reserved'&$select=columns,more,Leasing_x002D_Status,lots&$skiptoken=Paged=TRUE&p_ID=671

So Power Query reformulates the entire OData query (like reordering some stuff, pagination etc.), but also makes the escaped character in the column name in the $select command uppercase (interestingly not in the $filter command) and Sharepoint responds that this column does not exist.

How can I get this to work?

Best regards,

Julian

[v-jingzhan-msft]

Hi @Julian-K 

You may try the solution in the Maximum URL length part of the document: Power Query OData Feed connector - Power Query | Microsoft Learn

Although the solution is for 401 error while you have the 400 error, its logic is to start with the root OData endpoint and then navigate and filter inside Power Query. You can select the necessary columns within Power Query Editor after querying the data successfully. Or you can remove the $select command from the current URL and use Power Query Editor to select columns later. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Julian23K]

Yeah, yeah, I could just skip the $select. I could also just use the Sharepoint List connector instead of OData.Feed(). The problem is that I got loads of columns and both will make the query very slow.

I'm mostly annoyed that OData.Feed manipulates my query in a way that breaks it. Crazy that I apparently can't tell Power Query to just execute the query as I actually wrote it.

[ronan]

same problem here, a field that don't have values in all rows/data registers breaks OData.Feed.

considering it is json data when not found the value would be undefined, not an error.