Forum Discussion

markvanwyk's avatar
markvanwyk
Regular Visitor
1 year ago
Solved

Web API Issue in Power BI Desktop

Hi All

 

I have an issue with the Power BI Web Advanced Query - I can get the API to authneticate and get data from most queries but the below query requires as SericeRequest and I do not know how to add this into PowerBI - the code below is from postman (in red is the Service request from postman - Listed both cURL and HTTP)

 

cURL Request
--header 'Content-Type: text/xml' \
--header 'X-Requested-With: QualysPostman' \
--header 'Authorization: xxxxxxxxx' \
--data '<ServiceRequest>
<filters>
<Criteria field="tagName" operator="EQUALS">Cloud Agent</Criteria>
</filters>
</ServiceRequest> '
 
HTTP Request
POST /qps/rest/2.0/search/am/hostasset HTTP/1.1
Host: qualysapi.qualys.eu
Content-Type: text/xml
X-Requested-With: QualysPostman
Authorization: xxxxxxxxxxxx
Content-Length: 127
 
<ServiceRequest>
<filters>
<Criteria field="tagName" operator="EQUALS">Cloud Agent</Criteria>
</filters>
</ServiceRequest>

 

 

 

Any assistance would be appriciated thanks

Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago
    let
    url = "https://qualysapi.qualys.eu/qps/rest/2.0/search/am/hostasset",
    headers = [
        #"Content-Type" = "text/xml",
        #"X-Requested-With" = "QualysPostman",
        #"Authorization" = "xxxxxxxxx"
    ],
    body = "<ServiceRequest>
                <filters>
                    <Criteria field=""tagName"" operator=""EQUALS"">Cloud Agent</Criteria>
                </filters>
            </ServiceRequest>",
    Source = Web.Contents(url, [
        Headers = headers,
        Content = Text.ToBinary(body)
    ]),
    Response = Xml.Tables(Source)
in
    Response

     

    Alternatively you can use the recommended approach - Json.FromValue  

10 Replies

    • markvanwyk's avatar
      markvanwyk
      Regular Visitor
      1 year ago

      Thanks Ibendlin

       

      The Web.Contents query works (see below) the issue is how do I get the red Service Request (Postman) into the query 

       

      <ServiceRequest>
      <filters>
      <Criteria field="tagName" operator="EQUALS">Cloud Agent</Criteria>
      </filters>
      </ServiceRequest>

       

      Power Bi Advanced Query

      let
      Source = Xml.Tables(Web.Contents("https://qualysapi.qualys.eu" & "/msp/user_list.php", [Headers=[Authorization="xxxxxxxx", #"X-Requested-With"="PowerBI"]])),
      Table0 = Source{0}[Table],
      Table1 = Table0{0}[Table],
      #"Changed Type" = Table.TransformColumnTypes(Table1,{{"USER_LOGIN", type text}, {"USER_ID", Int64.Type}, {"EXTERNAL_ID", type text}, {"USER_STATUS", type text}, {"CREATION_DATE", type datetime}, {"LAST_LOGIN_DATE", type text}, {"USER_ROLE", type text}, {"BUSINESS_UNIT", type text}, {"UNIT_MANAGER_POC", Int64.Type}, {"MANAGER_POC", Int64.Type}, {"UI_INTERFACE_STYLE", type text}}),
      #"Expanded CONTACT_INFO" = Table.ExpandTableColumn(#"Changed Type", "CONTACT_INFO", {"FIRSTNAME", "LASTNAME", "TITLE", "PHONE", "FAX", "EMAIL", "COMPANY", "ADDRESS1", "ADDRESS2", "CITY", "COUNTRY", "STATE", "ZIP_CODE", "TIME_ZONE_CODE"}, {"CONTACT_INFO.FIRSTNAME", "CONTACT_INFO.LASTNAME", "CONTACT_INFO.TITLE", "CONTACT_INFO.PHONE", "CONTACT_INFO.FAX", "CONTACT_INFO.EMAIL", "CONTACT_INFO.COMPANY", "CONTACT_INFO.ADDRESS1", "CONTACT_INFO.ADDRESS2", "CONTACT_INFO.CITY", "CONTACT_INFO.COUNTRY", "CONTACT_INFO.STATE", "CONTACT_INFO.ZIP_CODE", "CONTACT_INFO.TIME_ZONE_CODE"})
      in
      #"Expanded CONTACT_INFO"

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, markvanwyk 

    Perhaps you can build the following query, create a Blank Query in PowerQuery, and go to the Advanced Editor

    let
    url = "https://qualysapi.qualys.eu/qps/rest/2.0/search/am/hostasset",
    headers = [
        #"Content-Type" = "text/xml",
        #"X-Requested-With" = "QualysPostman",
        #"Authorization" = "xxxxxxxxx"
    ],
    body = "<ServiceRequest>
                <filters>
                    <Criteria field=\"tagName\" operator=\"EQUALS\">Cloud Agent</Criteria>
                </filters>
            </ServiceRequest>",
    Source = Web.Contents(url, [
        Headers = headers,
        Content = Text.ToBinary(body)
    ]),
    Response = Xml.Tables(Source)
in
    Response

     

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • markvanwyk's avatar
      markvanwyk
      Regular Visitor
      1 year ago

      Hi 

       

      Thnaks for the info - we did try the solution but get the below error