Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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

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="),

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors