none
When Power Query reads an XML file it doesn’t get the fields that are “deep down” RRS feed

  • Question

  • Are only the first fields added? "transaction", {"transactionID", "description", "period", "transactionDate", "line"}, {"transactionID", "description.1", "period", "transactionDate", "line"}) Is ther a way to add those fields yourself?

    Monday, July 18, 2016 11:20 AM

Answers

  • It's hard to provide a specific answer without seeing the source data, but we have two different functions for reading XML. Xml.Tables is the default picked by the UI. It tries to treat your XML as though it were rectangular data. In doing so, it makes some guesses that can be inaccurate. If you'd rather get the raw XML parsed tree, then you can use Xml.Document instead. This will preserve all of the values and structures in the source, and you'll need to manually shape the output.

    To use Xml.Document, you'll need to type it either into the formula bar or in the advanced editor.

    Monday, July 18, 2016 3:32 PM
  • Hi Robert,

    Have you made any progress with this?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, August 13, 2016 9:46 PM
    Moderator

All replies

  • Are only the first fields added? "transaction", {"transactionID", "description", "period", "transactionDate", "line"}, {"transactionID", "description.1", "period", "transactionDate", "line"}) Is ther a way to add those fields yourself?


    When I use Excel, via the Developer TAB, I get all the fields and their contents.
    Monday, July 18, 2016 1:17 PM
  • It's hard to provide a specific answer without seeing the source data, but we have two different functions for reading XML. Xml.Tables is the default picked by the UI. It tries to treat your XML as though it were rectangular data. In doing so, it makes some guesses that can be inaccurate. If you'd rather get the raw XML parsed tree, then you can use Xml.Document instead. This will preserve all of the values and structures in the source, and you'll need to manually shape the output.

    To use Xml.Document, you'll need to type it either into the formula bar or in the advanced editor.

    Monday, July 18, 2016 3:32 PM
  • Curt,

    Do you have an email address I can send the data too? Please keep in mind that its confidential data.

    Monday, July 18, 2016 7:12 PM
  • Hi Robert. You can send the data to pq2fb@microsoft.com and reference Curt's name in the message.

    Ehren

    Tuesday, July 19, 2016 11:37 PM
    Owner
  • Ehren, I already mailed everything to Curt's personal email.

    I also followed his advice and used Xml.Document. However, custSupID and documentID are not there, while XML Pad Pro and other tools find those fields and show their content.

    Very strange. I think it has something to do with the fact that they are "deep down", only occur.

    If you take aard2014, Curt has all the files, you see that it goes wrong in journals. AA0 does not contain custSupID and documentID. As of AA1 they are there, but Power Query doesn't see them.

    Will this be fixed? 

    Again, when I use Excel, via the Developer TAB, I get all the fields and their contents.

    I have to put our project on hold now or choose for another tool.

    Wednesday, July 20, 2016 12:15 PM
  • Hi Robert,

    Have you made any progress with this?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Saturday, August 13, 2016 9:46 PM
    Moderator