Hi, @praphull
I think header has a typo, encoding= instead of charset=. In power bi, OData Connector, it relies on the API’s Content-Type header to parse the response. Try append $format=application/xml;charset=utf-8 to the URL to force the correct format, If you prefer the OData connector.
You could also, bypass the default OData connector and use the Web Connector instead to manually parse the XML.
First fetch the raw response as binary (ignoring the Content-Type header), then convert the binary to Text using UTF-8 encoding and then parse the text as XML. Here is an example (Power Query):
let
// Fetch the raw response as binary (ignoring the Content-Type header)
Source =
Web.Contents("https://api.decipher.com/your-endpoint", [Headers[Accept="application/xml"]]),
// Convert the binary response to text using UTF-8 encoding
ResponseText = Text.FromBinary(Source, TextEncoding.Utf8),
// Parse the text as XML
ParsedXml = Xml.Tables(ResponseText)
in
ParsedXml
If the API requires authentication or other headers (e.g., API keys), add them to the Web.Contents function:
Source = Web.Contents(
"https://api.decipher.com/your-endpoint",
[
Headers = [
Accept = "application/xml",
Authorization = "Bearer YOUR_API_KEY"
]
]
),
Run the query. If the XML parses successfully, expand the tables/nodes in Power Query Editor
If error still exist, use Query Diagnostics to inspect the raw response.
This way parsing the XML ensures Power BI doesn’t rely on the API’s incorrect Content-Type header.
Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!
Best Regards,
Shahariar Hafiz
