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’…”.
- 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.
- Fix or pre-clean: replace – (and similar) with a hyphen before parsing, or use Html.Table if the file is HTML (not XML).
- 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
DiagnosticsB) 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
CleanedUsage:
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.
