Web API Issue in Power BI Desktop

[markvanwyk]

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

curl --location 'https://qualysapi.qualys.eu/qps/rest/2.0/search/am/hostasset' \

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

[Anonymous]

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]

Hi 

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

[lbendlin]

instead of \"  use ""

[markvanwyk]

Hi Ibendlin

Thanks for the sugestion but still doesnt work. I have tried multiple option

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

<Criteria field=\'tagName\' operator=\'EQUALS\'>Cloud Agent</Criteria>

 <Criteria field='tagName' operator='EQUALS'>Cloud Agent</Criteria>

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

Regards

Mark

[lbendlin]

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

[markvanwyk]

Hi Ibendlin

I get the below error in PowerBI

DataSource.Error: Web.Contents failed to get contents from 'https://qualysapi.qualys.eu/qps/rest/2.0/search/am/hostasset' (400):
Details:
DataSourceKind=Web
DataSourcePath=https://qualysapi.qualys.eu/qps/rest/2.0/search/am/hostasset
Url=https://qualysapi.qualys.eu/qps/rest/2.0/search/am/hostasset

[lbendlin · Accepted solution]

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  

[lbendlin]

Please follow the documentation. Use the Content parameter to change the request from GET to POST. https://learn.microsoft.com/en-us/powerquery-m/web-contents

[markvanwyk]

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"

[lbendlin]

Please follow the documentation. Use the Content parameter. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-2