Data Format Error in Powerbi Service

Hi  Batman020 , 

Hi Batman020,

You’re hitting an XML parser error during refresh because one of the files coming from SharePoint contains an HTML entity (–) inside text that Power BI is trying to parse as XML. In XML, entities like – are not defined unless a DTD declares them, so the parser stops with “Reference to undeclared entity ‘ndash’…”.

1. Find the bad file by wrapping the parse step in try ... otherwise inside the Transform File function so the file Name and Error are surfaced.
2. Fix or pre-clean: replace – (and similar) with a hyphen before parsing, or use Html.Table if the file is HTML (not XML).
3. Publish and refresh again.

Refs: try ... otherwise | SharePoint Folder connector | Xml.Tables | Html.Table

A) Diagnostics to pinpoint the file

let
  Source      = SharePoint.Files("https://contoso.sharepoint.com/sites/YourSite", [ApiVersion = 15]),
  KeepCols    = Table.SelectColumns(Source, {"Name","Extension","Content"}),
  LikelyTypes = Table.SelectRows(KeepCols, each List.Contains({".xml",".xlsx",".xls",".html",".htm"}, Text.Lower([Extension]))),
  TryParse    = Table.AddColumn(
                  LikelyTypes, "ParseResult",
                  each try
                        if [Extension] = ".xml" then Xml.Tables([Content])
                        else if List.Contains({".html",".htm"}, Text.Lower([Extension])) then Html.Table([Content], {{"Dummy", "body"}})
                        else if List.Contains({".xlsx",".xls"}, Text.Lower([Extension])) then Excel.Workbook([Content], true)
                        else null
                      otherwise null
                ),
  HasError    = Table.AddColumn(TryParse, "HasError", each Value.Is([ParseResult], type error)),
  ErrorText   = Table.AddColumn(HasError, "ErrorText", each if [HasError] then try Error.Reason([ParseResult]) otherwise "Unknown error" else null),
  Diagnostics = Table.SelectRows(ErrorText, each [HasError] = true)
in
  Diagnostics

B) Clean entities before Xml.Tables

(entityText as text) as text =>
let
  Map = {
    {"–","-"},{"—","-"},{" "," "},{"‘","'"},{"’","'"},
    {"“","""},{"”","""},{"&","&"}
  },
  Cleaned = List.Accumulate(Map, entityText, (state, pair) => Text.Replace(state, pair{0}, pair{1}))
in
  Cleaned

Usage:

let
  AsText    = Text.FromBinary([Content], TextEncoding.Utf8),
  CleanText = @CleanEntities(AsText),
  BackToBin = Text.ToBinary(CleanText, TextEncoding.Utf8),
  XmlParsed = Xml.Tables(BackToBin)
in
  XmlParsed

C) If it’s HTML, use Html.Table instead of Xml.Tables

Html.Table([Content], {{"All", "//*"}})

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

But i am using the the sharepoint file connecter in my powerbi desktop it all works fine in the dekstop as expected but as soon as in the service it crashes with this error on refresh , And actually i have a lot of sheets in my excel file and the data is also huge so how do i find that ndash and replace , can u guide me if i am using the wrong connected for it which is causing the error 
Thanks 

Hi Batman020,

Thank you for reaching out to the Microsoft Fabric Community, and special thanks to @tayloramy  and djurecic  for their prompt and helpful responses.

Thanks for the clarification. As @tayloramy  mentioned, could you please confirm whether you're using Excel files stored on SharePoint or XML files as your data source? This will help us determine if the issue is related to the connector being used.

Thanks & Regards,

Prasanna Kumar

Hi Batman020 

The error you’re seeing — “DataFormat.Error: Xml processing failed... Reference to undeclared entity 'ndash'” — indicates that one of the files being imported from your SharePoint folder contains invalid XML content or a special character (in this case, “–” representing an en dash) that Power BI’s parser can’t interpret. This usually happens when Power BI encounters a nonstandard or malformed XML/HTML entity in a file it’s trying to read (often in Excel, XML, or HTML exports). Because the error message doesn’t specify which document is causing it, you’ll need to isolate it manually. The best way to do this is to open your Power BI file in Power BI Desktop, go to Power Query Editor, and step through the applied steps of your SharePoint Folder query. At the point where the query expands or transforms files, add a temporary step (e.g., filter by file name or extension) and preview each file’s content until the error appears. This will reveal which specific file or files contain invalid XML characters. Once identified, open that file in a text or XML editor, replace or remove the “–” entity (or save the file in UTF-8 format), and re-upload it to SharePoint. Then refresh again in Power BI. Essentially, the issue isn’t with the connection but with malformed data in one or more of your source files, and checking the files individually in Power Query is the most efficient way to pinpoint and fix it.

Hi Batman020,

Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

Best regards,
Prasanna Kumar