Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    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

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-lili6-msft
        Community Support
        7 years ago

        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