Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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, @Anonymous 

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, @Anonymous 

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.
Anonymous
Not applicable

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, @Anonymous 

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.
Anonymous
Not applicable

thank you! worked perfectly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.