Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
hi, @Anonymous
For your XML has multiple levels tables, so you need to do these steps as below:
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
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
Best Regards,
Lin
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:
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
thank you! worked perfectly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |