XML Parser Data Type Error

[alex9999]

Hello,

I have recently started using Power BI for my organisation and have run into an error when getting parsing XML using Power Query Editor. I can successfully find the end data by diving into each column and parsing continuously. However, once I do the same for another column I am left with "system.string" or "system.int64" as my final piece of data.

How can I fix this?

Thanks

[AlexisOlson]

It's pretty hard to help here unless you can share some example XML and the result you expect to get by parsing.

[alex9999]

Hi @AlexisOlson 

Sorry for the late reply. The XML used is:

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version>1.0</Version><Items><Item><_f5f285b94926f2bcaac45a9697714f3a type="System.String">O Ledger 2.07m</_f5f285b94926f2bcaac45a9697714f3a><_3bf8a031c7e6d64b9e20b74d4c8a3ad2 type="System.Int64">10</_3bf8a031c7e6d64b9e20b74d4c8a3ad2></Item><Item><_f5f285b94926f2bcaac45a9697714f3a type="System.String">O Ledger 1.57m</_f5f285b94926f2bcaac45a9697714f3a><_3bf8a031c7e6d64b9e20b74d4c8a3ad2 type="System.Int64">5</_3bf8a031c7e6d64b9e20b74d4c8a3ad2></Item><Item><_f5f285b94926f2bcaac45a9697714f3a type="System.String">U Transom - 1.4m</_f5f285b94926f2bcaac45a9697714f3a><_3bf8a031c7e6d64b9e20b74d4c8a3ad2 type="System.Int64">6</_3bf8a031c7e6d64b9e20b74d4c8a3ad2></Item></Items></RepeaterData>

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version>1.0</Version><Items><Item><_f903b399305565785d962b5d0fdb8ed2 type="System.String"></_f903b399305565785d962b5d0fdb8ed2><_e3f188b46c4d8f4fb312333462d2bbc7 type="System.String"></_e3f188b46c4d8f4fb312333462d2bbc7></Item></Items></RepeaterData>

<?xml version="1.0" encoding="utf-8"?><RepeaterData><Version>1.0</Version><Items><Item><_d7878ea683494fb88a2dac7be6247a8c type="System.String"></_d7878ea683494fb88a2dac7be6247a8c><_b27ba1a458daeb7777901d8d833228fc type="System.String"></_b27ba1a458daeb7777901d8d833228fc></Item></Items></RepeaterData>

The XML will parse down to "O Ledger 2.07m" with the first XML example. However the other 2 XML examples are from a different field of a SharePoint list. No matter whether they are populated or not, they will only show "System.String".

XML not parsed showing "Component" or "Null"

 

XML Showing Component and Nulls.

[AlexisOlson]

This is difficult because your columns don't have consistent names (they look like hex strings).

It's a bit ugly, but see if this example is going in the right direction:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5ZNNS8NAEIb/SsjZNvu9swfx4kUQBL8uRmR2d1YKNpUkiv33xkalaQ/poWrBve3L7vvOPMPc3eXlC2MynLzNn7JXqpvZojoucz5lZZ5RFRZxVj12wkubJlDmJ6vX1P+5pGfClupTbHFdv+1teunDaaUWA7nXzlqaN5vC+v0h6SRAe6ecMEn4gBiURmectVwliVm7fKauvqtl032dXrV1V2/ZN0UX2TnFR6ozMWV2/lnGqOUgX/oEyCQPlkw0yjsSzFsVVQCUGMVG/lnVGvUVz786H3VZjyw2GfwQEz7Vf8BEHyCSm+y6xqpZzLNJR0X9PhSzFyjF1jYV2xua3x8d/Mo7Jr10TjKtjbagozPC68hS9EBbJIez/J7diMkgkWTiAF6ZoCIklbzkQkqpjIjC+2B3Shwz+RezixYsEBqQynUIAFBEDNaTEcoihJ1IjpkMEr2wHjkqDRHJ2+44xiNEkFIISLsljpnsY3b37w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col_XML = _t]),
    #"Parsed XML" = Table.TransformColumns(Source,{{"Col_XML", each Xml.Tables(_){0}[Items][Item]{0}}}),
    #"Added Custom" = Table.AddColumn(#"Parsed XML", "Custom", each Table.FromColumns(List.Transform(Table.ToColumns([Col_XML]), each List.Transform(_, each try [#"Element:Text"]{0} otherwise null)))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"})
in
    #"Expanded Custom"

Paste this into the Advanced Editor of a new blank query to examine the steps.