Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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
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="),