convert XML query to a table

[mvwd]

Hello!

I'm fairly new to power bi. My goal is to set up a query to our CRM to look at our data. I believe I was successful in doing so. I chose web under the get data option, and this is what it returned: 

https://www.dropbox.com/s/547vjuf9gp2v9gt/power%20bi%201.PNG?dl=0

I believe the query is working, but I want it to convert the XML response into a table that looks something like this:

RecordID	ActivityCreatedDate	ExpectedInstallDate	InvoiceTxnDate
23063	        2018-12-11T19:00:00	2018-12-19T19:00:00	2018-12-13T19:00:00

I only specified one record in my URL to see if something like this is even possible. My data will have a lot more entries.

If this doesn't make sense, please let me know and I will clarify.

Thanks!

[v-lili6-msft]

hi, @mvwd 

You just need to refer to this post:

https://community.powerbi.com/t5/Desktop/XML-document-data-in-a-column/td-p/448699

clicking Parse to xml and expend the table

Best Regards,

Lin

[mvwd]

hello, thanks for the reply.

I followed the other post, but it didn't split the xml data correclty. This is what it returned:

https://www.dropbox.com/s/aybq27jbkvx180i/power%20bi%202.PNG?dl=0

it want it to look something like this if possible.

RecordID	ActivityCreatedDate	ExpectedInstallDate	InvoiceTxnDate
23063	        2018-12-11T19:00:00	2018-12-19T19:00:00	2018-12-13T19:00:00

it looks like its taking the first line of the XML response?

The xml response looks like this:

<?xml version="1.0" encoding="windows-1252" ?><MethodAPI response = "Success" MaxRecords= "False"><MethodIntegration Table='ActivityJobItems'>
  <Record>
    <RecordID>23063</RecordID>
    <ActivityCreatedDate>2018-12-11T19:00:00</ActivityCreatedDate>
    <ExpectedInstallDate>2018-12-19T19:00:00</ExpectedInstallDate>
    <InvoiceTxnDate>2018-12-13T19:00:00</InvoiceTxnDate>
  </Record>
</MethodIntegration></MethodAPI>

from my picture above it looks like its just parsing through:

<?xml version="1.0" encoding="windows-1252" ?><MethodAPI response = "Success" MaxRecords= "False"><MethodIntegration Table='ActivityJobItems'>

and ignoring the rest of the xml.

shouldn't RecordID, ActivityCreatedDate, ExpectedInstallDate, InvoiceTxnDate all be options in the dropdown?

Thanks

[v-lili6-msft · Accepted solution]

hi, @mvwd 

For your XML has multiple levels tables, so you need to do these steps as below:

Step1Step2Step3

Result:

and here is M code, please try it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZJRa8IwFIX/Ssi7a9LiNgdFZDrIQBhb36wPMbloICbSxNr9+8Wlm61WCHk4557vhnuzWuHySEgmps1eoxoqp6zJS0wfSIkRGGGlMtsgnJSR9uRGNB2nwZn+piBml+B3Vs4+GKrAHaxxgHJU4q+jEOBcqF7y5hOEraQ7629cOyjxLYEZD9uK+/AEVPCNhvzspk8z4VWt/Pe73TAPexfVNm8QiozY4aL2dTaPTpqRxywaSd/pZv46vlbAPch5uNo4oc9hBiNKCzp5ISScFnY30uUumgOI4DLjPNd6gDu55t6NdLnM1FYJKBozgMyukUPVlzkm/UG24s2GuutL/n9AVPF6/QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Element:Text" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Element:Text", type text}}),
    #"Parsed XML" = Table.TransformColumns(#"Changed Type",{},Xml.Tables),
    #"Expanded Element:Text" = Table.ExpandTableColumn(#"Parsed XML", "Element:Text", {"MethodIntegration"}, {"MethodIntegration"}),
    #"Expanded MethodIntegration" = Table.ExpandTableColumn(#"Expanded Element:Text", "MethodIntegration", {"Record"}, {"Record"}),
    #"Expanded Record" = Table.ExpandTableColumn(#"Expanded MethodIntegration", "Record", {"RecordID", "ActivityCreatedDate", "ExpectedInstallDate", "InvoiceTxnDate"}, {"RecordID", "ActivityCreatedDate", "ExpectedInstallDate", "InvoiceTxnDate"})
in
    #"Expanded Record"

Best Regards,

Lin

[mvwd]

thank you! worked perfectly.