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 extract the data.  However the structure of the URL includes a Month (dec) , and Year (2017) reference.  I would expect next month the URL I want to connect to  will be updated to :

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

I have tried using * and ? wildcards to the Year and Month "URL parts" within the Advanced settings component of the web connection, without any luck (see below)- the connection fails.

 

Can anyone suggest a way to connect to dynamic URLs ?  

Cheers Steve

 

  • 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

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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