Handling of spaces when importing XML data

[AlexHeinze]

I'm using several sample XML files from an external system to test my report. This has been going well so far, but now two things cause problems. In one of the files I find an element with just whitespace in it:

<reason xml:space="preserve"> </reason>

The second file has whitespace somewhere else in the element:

<name>AM </name>

In both cases Power Query interprets the elements as tables instead of text. What is an elegant way that caters for both my "normal" XML content as well as these edge cases?

Appreciate your help!

[v-yalanwu-msft]

Hi, @AlexHeinze ;
You can check if these related post can is useful to you.

Import Data from XML Using Power Query | MyExcelOnline

A Primer on JSON and XML Files in Power BI (iterationinsights.com)

Power Query XML connector - Power Query | Microsoft Learn

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexHeinze]

Hi Yalan, I did check these (and lots of others), but unfortunately they don't explain the handling of leading and trailing blanks in the XML.