Forum Discussion
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 ...
hi, Anonymous
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
v-lili6-msft
Community Support
Community Supporthi, Anonymous
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
thank you! worked perfectly.