Forum Discussion

GrahamR's avatar
GrahamR
Frequent Visitor
10 months ago
Solved

XML import problem

I have an XML file delivered to me where I need to display the Location text field.  I have tried various ways but need some help please.   Minimal example xml (just 1 object) is here and the text ...
Need Help
  • v-tejrama's avatar
    v-tejrama
    10 months ago

    Hi GrahamR ,

     

    You are correct this is the expected behavior.

    The reason you are seeing only null values is that the plain text “Site A” is not retained when Power BI parses the XML. The Xml.Tables function processes only structured elements and attributes, and in your file, “Site A” appears as loose text before the <Shelf> and <Box> elements. As a result, it is considered mixed content and omitted during import.

    Since this text does not make it into Power Query, it cannot be retrieved by any M or DAX expressions after the XML is loaded.

     

    The best solutions are to either update the XML so that “Site A” is within its own tag or attribute, or to read the file as raw text and extract the relevant section before <Shelf> using text functions prior to converting it to XML.

    In summary, your understanding is correct the data loss occurs during the XML parsing stage, and is not due to any issues with your process.

    Thank you,

    Tejaswi

Nabha-Ahmed's avatar
Nabha-Ahmed
Super User
10 months ago

Hi GrahamR 

 

Use Power Query and the XML document structure

  1. Load your XML file normally in Power BI.

  2. In Power Query Editor, select the column that contains your XML (e.g. ObjectLocation.Location).

  3. Add a Custom Column with the formula below:= try Text.BeforeDelimiter(Text.FromBinary(Xml.Tables([ObjectLocation])[Location]{0}[#text]), "<") otherwise null

  4. Rename the new column to Location_Text (or “Site Name”).

 

if this not work for you mention me please to see another way 

Accept as Solution ✅ and giving it a Like 👍– it helps others in the community too.

Thanks ✨🌹

 

 

 

 

 

 

  • GrahamR's avatar
    GrahamR
    Frequent Visitor
    10 months ago

    Hi

     

    A very interesting approach !  However I get 'Invalid Identifier' error against the [#text] part of the try.  I have even been trying to just use the Text.FromBinary part but can't get any text back.

     

    Any thoughts?