cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mvwd
Helper I
Helper I

convert XML query to a table

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!

1 ACCEPTED SOLUTION

hi, @mvwd 

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

Step1Step1Step2Step2Step3Step3

Result:

4.JPG

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

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

10.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

 

hi, @mvwd 

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

Step1Step1Step2Step2Step3Step3

Result:

4.JPG

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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thank you! worked perfectly.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.