Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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="),
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |