How to handle special characters in OData Feed query results?

[ECorona]

I made a custom connector for the company I work for, It consumes a REST API (made by us but the data is created by the users), we want to migrate our implementation to OData.feed from Web.Contents to take advantage of query folding capabilities. 

The thing is that doing that change breaks the connector, we are having the next error: 
DataSource.Error: OData: Encountered a property 'Phone #' that was expected to be a reference to a location in the $metadata document but does not contain a '#' character or is otherwise not a valid metadata reference property. A metadata reference property must contain a '#' and be a valid absolute URI or begin with a '#' and be a valid URI fragment.
Details:


the retrieved json is something like this:

"contactProperties": {

                "Inspection type": "",

                "Phone #": "",

}

There are other properties created by the user and can contain basically any character, is there a way to support these characters in OData?, we can encode the characters in the backend that created the API but I would like to know if there is something we can do in the Power Query M side. 

NOTE: 

The users can create the own properties and we must not rename the columns to preserve the integrity and meaning of the user data. 

[v-tsaipranay]

Hi @ECorona ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further. Also thank you @rohit1991  for your insights.

Thank you.

[ECorona]

Thank you for your suggestions and tips, actually, encoding the characters in the JSON fixed the issue. 

[rohit1991]

Hi @ECorona 

This is a known limitation with OData.Feed it follows the OData spec strictly, which means special characters like # or spaces in property names can break parsing or result in missing fields.

Below are the options

1. Backend fix (preferred): Encode property names in the API itself using something like Phone_x0023_, so OData can parse them correctly.

2. Client-side workaround: Use Web.Contents instead of OData.Feed and manually parse the JSON. This avoids OData’s metadata enforcement and lets you preserve original names exactly as-is. You’ll lose query folding, but it works reliably.

Reference:
https://learn.microsoft.com/en-us/answers/questions/1290885/naming-dinamically-the-sink-file-for-a-p...

[v-tsaipranay · Accepted solution]

Hi @ECorona ,

Thank you for reaching out to the Microsoft fabric community forum.

This issue occurs because OData.Feed follows the OData specification, which requires property names to exactly match those in the $metadata document. If a property like "Phone #" is used, the "#" character is treated as a metadata reference instead of part of the property name, leading to an error. This can be problematic when fields contain special characters that are not allowed by OData metadata rules.

Power Query M does not provide a built-in way to handle or sanitize these property names in OData.Feed, since parsing happens before Power Query can make changes. If you can modify the backend API, it’s best to encode or escape special characters in property names (such as using Phone_x0023_ instead of Phone #) and update the $metadata accordingly. This will help ensure compatibility with OData standards and allow OData.Feed to function properly.

If changing property names isn’t possible and you need to keep the original names, consider using Web.Contents. This method offers more flexibility for working with dynamic JSON and special characters, though it does not support query folding. You can then parse the JSON using functions like Record.ToTable() or Table.FromRecords() as needed.

Hope this helps. Please reach out for further assistance.

Thank you.