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

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