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

Thanks for this Lydia.

 

Out of interest do you know if there is any way to avoid needing user input? Ie extract any URL   that has a 3 character month and 4 character year, followed by a CSV suffix?    

 

https://www.at.govt.nz/media/1975723/patronage-data-???-????.csv


Cheers

Steve

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