Forum Discussion

sc20d2's avatar
sc20d2
New Member
2 years ago
Solved

Parsing XML element values in Power Query

I need to parse some XML with data stored in element values (rather than attributes) in Power Query. This works fine as long as the elements don't have sub-elements as follows:

<people>
    <person id="1" gender="male">
        <name>Joe</name>
    </person>
    <person id="2" gender="female">
        <name>Sally</name>
    </person>
    <person id="3" gender="male">
        <name>Mike</name>
    </person>
</people>

 

When my element has sub-elements, Power Query assumes the column is a table datatype and ignores the element values (Joe, Sally, and Mike). It forces me to drill down into the table and provides access to the sub-element's attributes (abbreviated name, full name), which is not what I'm interested in.

<people>
    <person id="1" gender="male">
        <name>Joe<type value="abbreviated name" />
        </name>
    </person>
    <person id="2" gender="female">
        <name>Sally<type value="full name" />
        </name>
    </person>
    <person id="3" gender="male">
        <name>Mike<type value="abbreviated name" />
        </name>
    </person>
</people>

I don't need the data in the sub-elements, so any advice on removing them, parsing them, or deleting them would be much appreciated. 

  • Hi sc20d2 ,

     

    Open your query in Advanced Editor then adjust the Source step to change Xml.Tables to Xml.Document.

    You'll still need to do a bit of jiggery-pokery to get the data into the shape you need, but this will at least expose all the data elements for use.

     

    Pete

3 Replies

  • Hi sc20d2 ,

     

    Open your query in Advanced Editor then adjust the Source step to change Xml.Tables to Xml.Document.

    You'll still need to do a bit of jiggery-pokery to get the data into the shape you need, but this will at least expose all the data elements for use.

     

    Pete

    • sc20d2's avatar
      sc20d2
      New Member

      Ah, I think I see that now. Thanks very much for the pointer BA_Pete . That was exactly what I needed! 

      • BA_Pete's avatar
        BA_Pete
        Super User


        No problem, happy to help.

        Don't forget to give a thumbs-up on any posts that have helped you ‌‌👍


        Pete