Data from SOAP WS to Power BI - Selecting only wanted data

[CarlsBerg999]

Hi,

I have been working to bring data from SOAP WS to Power BI. I've used the following thread to make my M-code in the query editor: Community thread on implementing SOAP WS in Power BI 

This code works and it retrieves the data. However, the data is returned in "Tables" that need to be expanded one-by-one in the Query Editor. This results in an endless "expansion of tables":

Does anyone (@Yggdrasill, @Jonas1 , @juliovidigal)know how I can modify the code to drill down directly to the wanted data? The response from the service is huge and expansion of tables one after another seems like a really poor way of doing this. 

The documentation of the SOAP Web Service is here: https://quinyx.helpdocs.io/l/en/article/3l3asgt0t1-get-time-punches

Below is the code that works (before all the expansions that are required). 

let

SourceURL = "https://rc.quinyx.com/FlexForceWebServices.php?wsdl",

options = [ #"Content-Type"="text/xml;charset=UTF-8",
            #"Connection"="Keep-Alive"
          ],

WebContent = Web.Contents(SourceURL, 
    [Content=Text.ToBinary("

<soapenv:Envelope xmlns:xsi=#(0022)http://www.w3.org/2001/XMLSchema-instance#(0022) xmlns:xsd=#(0022)http://www.w3.org/2001/XMLSchema#(0022) xmlns:soapenv=#(0022)http://schemas.xmlsoap.org/soap/envelope/#(0022) xmlns:uri=#(0022)uri:FlexForce#(0022)>
   <soapenv:Header/>
   <soapenv:Body>
      <uri:wsdlGetTimePunches soapenv:encodingStyle=#(0022)http://schemas.xmlsoap.org/soap/encoding/#(0022)>
         <apiKey xsi:type=#(0022)xsd:string#(0022)>BLA-BLA-BLA-BLA</apiKey>
         <getTimePunchesRequest xsi:type=#(0022)flex:getTimePunchesRequest#(0022) xmlns:flex=#(0022)https://apiftp.quinyx.com/soap/FlexForce#(0022)>
            <!--You may enter the following 14 items in any order-->
            <!--Optional:-->
            <employeeId xsi:type=#(0022)xsd:int#(0022)>?</employeeId>
            <!--Optional:
            <badgeNo xsi:type=#(0022)xsd:string#(0022)>?</badgeNo>-->
            <!--Optional:-->
            <sectionId xsi:type=#(0022)xsd:int#(0022)>?</sectionId>
            <!--Optional:-->
            <fromDate xsi:type=#(0022)xsd:date#(0022)>2022-01-01</fromDate>
            <!--Optional:-->
            <toDate xsi:type=#(0022)xsd:date#(0022)>2022-06-30</toDate>
            <!--Optional:-->
            <transferredToPayroll xsi:type=#(0022)xsd:boolean#(0022)>?</transferredToPayroll>
            <!--Optional:-->
            <notTransferredToPayroll xsi:type=#(0022)xsd:boolean#(0022)>?</notTransferredToPayroll>
            <!--Optional:-->
            <approved xsi:type=#(0022)xsd:boolean#(0022)>?</approved>
            <!--Optional:-->
            <unapproved xsi:type=#(0022)xsd:boolean#(0022)>?</unapproved>
            <!--Optional:-->
            <deleted xsi:type=#(0022)xsd:boolean#(0022)>?</deleted>
            <!--Optional:-->
            <active xsi:type=#(0022)xsd:boolean#(0022)>?</active>
            <!--Optional:-->
            <allUnits xsi:type=#(0022)xsd:boolean#(0022)>?</allUnits>
            <!--Optional:
            <modifiedAfter xsi:type=#(0022)xsd:string#(0022)>?</modifiedAfter>-->
            <!--Optional:-->
            <employeeUnitMode xsi:type=#(0022)xsd:int#(0022)>?</employeeUnitMode>
         </getTimePunchesRequest>
      </uri:wsdlGetTimePunches>
   </soapenv:Body>
</soapenv:Envelope>

"),

Headers=options]) ,
XmlContent = Xml.Tables(WebContent),
    Table = XmlContent{0}[Table]
in
    Table

Thank you in advance! 

[Anonymous · Accepted solution]

HI @CarlsBerg999,

In fact, the current power query seems did not include any features to directly expand to the bottom level.
Perhaps you can add a custom column to use the M query 'operator' to extract the nest content based on field names.

Operators - PowerQuery M | Microsoft Docs

Regards,

Xiaoxin Sheng