Data Format Error in Powerbi Service

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.