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 ...
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

     

  • 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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        thank you! worked perfectly.