Variable in API Call

[mvantuijl]

Good day Power BI community

I am a newbie to this 🙂  ... and i hope you can help me. I am struceling with the following challenge.

1. I have data in a table that is renewed though an API call thats gives my te following result

Table.REPORT_LIST.REPORT.ID	Table.REPORT_LIST.REPORT.TITLE	Table.REPORT_LIST.REPORT.TYPE	Table.REPORT_LIST.REPORT.OUTPUT_FORMAT
123456	report1	Compliance	XML
234567	report2	Compliance	XML
345678	report3	Compliance	XML
456789	reprot4	Compliance	XML
567890	report5	Scan	XML

1. i need the Table.REPORT_LIST.REPORT.ID to be used as an variable in a connection string  to get a specific report. The api call looks abit like this

Example

= Xml.Tables(Web.Contents("https://example.com/fo/report/?action=fetch&id=123456", [Headers=[Authorization="token", #"X-Requested-With"="Detection"]]))

what i hope to accomplish is when the report_id is refreshed that the report_id in the connection string is going to use that input from the variable

Thanks in advance for your assistance and I hope it is possible 😊

Many thanks

Michel

[lbendlin]

Please refer to the documentation.  Use RelativePath and Query parameters.

Web.Contents - PowerQuery M | Microsoft Learn

[mvantuijl]

Hi Ibendlin,

Thank you for your reply !! I tried to look at web.contents but i can not figure it out 😞 

I forgot to mention that the connection string below is to refresh another table. i need 123456 in the string to be a variable and the varaible input is from another table and the content is that table is always refreshed because reports get refreshed and get a new report_id

= Xml.Tables(Web.Contents("https://example.com/fo/report/?action=fetch&id=123456", [Headers=[Authorization="token", #"X-Requested-With"="Detection"]]))

The data comes from de Qualys API and its needed to get the latest data from a report.

[lbendlin]

the documentation provides a very specific example exactly for your scenario.

= Xml.Tables(Web.Contents("https://example.com", [RelativePath="fo/report", Query =[action="fetch",id=Text.From(123456)], [Headers=[Authorization="token", #"X-Requested-With"="Detection"]]))

[mvantuijl · Accepted solution]

Thank you for all you support it help me understand the code below and how to get it fixed 

let
    // Laad de tabel
    Source = TABLENAME,
    // Filter de rijen op de waarde in de TITLE kolom
    FilteredTable = Table.SelectRows(Source, each [Table.REPORT_LIST.REPORT.TITLE] = "cel value"),
    // Selecteer de eerste rij van de gefilterde tabel
    FirstRow = Table.First(FilteredTable),
    // Haal de waarde van de ID kolom op
    ReportID = Record.Field(FirstRow, "Table.REPORT_LIST.REPORT.ID"),

    // Definieer de API URL met de dynamische ID
    BaseUrl = "https://qualysapi.qg2.apps.qualys.eu/api/2.0/fo/report/?action=fetch&id=" & Text.From(ReportID),

    // Maak de API-aanroep
    ApiResponse = Xml.Tables(Web.Contents(BaseUrl, [Headers=[Authorization=QualysToken, #"X-Requested-With"="Detection"]]))
in
    ApiResponse