Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Dynamic connection to Web URL

Hi I am connecting to a csv file at the following URL with a standard "web" connection  : https://www.at.govt.nz/media/1975723/patronage-data-dec-2017.csv The connection works fine, and I can extra...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Anonymous,

    Add a new blank query in Power BI Desktop, then paste the following code to the Advanced Editor of the blank query.

    (Month as text,year as number)=>

    let
     Source = Csv.Document(Web.Contents("https://www.at.govt.nz/media/1975723/patronage-data-" & ""&Month&"" & "-" & ""&Number.ToText(year)&"" & ".csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
     #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}})
    in
     #"Changed Type"


    Input month and year in the function and invoke the function to get required table.


    Regards,
    Lydia

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous,

Another method is to create two parameters(Month and year) listing all the possible values, then users are able to choose values to return different queries.

let
 Source = Csv.Document(Web.Contents("https://www.at.govt.nz/media/1975723/patronage-data-" & ""&Month&"" & "-" & ""&year&"" & ".csv"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None])
in
    Source




Regards,
Lydia

Farhad88's avatar
Farhad88
Icon for Helper I rankHelper I
2 years ago

Hi Lydia,

 

Thanks for providing the solution. I need to cover a little different scenario as the following (screenshot attached):

My client wants to select the value of the parameter using a slicer. 

As you know, it is fairly easy to do so when using DirectQuery on SQL data source, but I cannot use the same solution for a Rest API data source (because the value of the parameter would be always the same as its defualt value!). 

Do you think there might be any solution to this issue?

 

Thanks and best regards,

Hossein