Build query string for Web.Contents based on another column from a query

[Anonymous]

Hello Power BI Community,

I've been extracting data in a form of CSV from a reports server via Web.Contents and it's working out fine as I have hard-coded the url query string. The challenge is, when a new hotel is added in the database, I need to manually append the new ID in the url. I already tried Invoke as function and passing the hotel ids as a parameter but reitariting 999+ rows of hotel Ids is not very efficient as it is taking too much time given that every id will be a new request.

Question is – is there a way to build a query string based on the hotel ID column? Below is the sample get request that I'm currently using. Goal is to build a query string of hotel ids ( hotel=1&hotel=2&hotel=3 and so on )

Source = Csv.Document(Web.Contents("http://ServerName/ReportServer_SSRS?/Reservations&hotel=[id]&hotel=[another_id]&hotel[another_id]&rs:ParameterLanguage=&rs:Command=Render&rs:Format=CSV"),[Delimiter=",", Columns=75, Encoding=65001, QuoteStyle=QuoteStyle.Csv])

PS: There's no API as it's an old version of SQL Server and no direct databse connectivity as the developer of our web application wouldn't give us access.

I hope that above makes sense. Appreciate the feedback. Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous 

Do you mean something like this:

let
    tHotels = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysgvSc2JN1TSUcpMAVKxOjAhI4iQEZKQMUTIWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [hotel = _t, hotel_id = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"hotel", type text}, {"hotel_id", type text}})
        in
            #"Changed Type",

    ListOfHotelIDs = tHotels[hotel_id],
    SearchString = Text.Combine(List.Accumulate(ListOfHotelIDs, {}, (s, a) => s & {"hotel=[" & a & "]"}), "&"),
    Source = Csv.Document(Web.Contents("http://ServerName/ReportServer_SSRS?/Reservations&" & SearchString & "&rs:ParameterLanguage=&rs:Command=Render&rs:Format=CSV"),[Delimiter=",", Columns=75, Encoding=65001, QuoteStyle=QuoteStyle.Csv])
in
    Source

Kind regards,

JB

[Anonymous]

Hi JB,

Thank you for your assistance. I managed to the same output but with a more simple approach. Nevertheless, I'm gonna use your approach to implement the entire code ( didn't know you can do nested 'Let' as I'm still new new Power BI). Will have to search more about this approach. Thank you!

HotelList = Text.Combine(HotelID , "&hotel="),