none
PowerQuery from dynamic URL RRS feed

  • Question

  • Hello,

    I am monthly downloading a CSV from a dynamic URL to copy to my workbook.

    The URL is dynamic in the sense that it has a variable date (YYMMDD): e.g., 

    www.ecb.europa.eu/paym/coll/assets/html/dla/ea_MID/ea_csv_200214.csv

    Currently I have an hyperlink taking from the cell, which I can easily import.

    =HYPERLINK("https://www.ecb.europa.eu/paym/coll/assets/html/dla/ea_MID/ea_csv_"&TEXT(O2;"yy")&TEXT(O2;"mm")&TEXT(O2;"dd")&".csv")

    I was however wondering if it would be possible to use Get&Transform for this purpose.

    If so, how to setup a dynamic URL taking the variable date reference from a cell?

    Thanks in advance.

    Tuesday, February 18, 2020 12:11 PM

Answers

  • Hello,

    thanks for answer. It works fine now.

    I had read some articles before posting, but I had some issues. 

    I simplified a bit the task, added a Table with the date formatted as per URL, and amended in Advance Editor of the power query as follows:

    let
        myDateVar = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Table3"]}[Content]),
        Source = Csv.Document(Web.Contents("https://www.ecb.europa.eu/paym/coll/assets/html/dla/ea_MID/ea_csv_" & myDateVar & ".csv"),[Delimiter=" ", Columns=20, Encoding=1200, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ISIN_CODE", type text}, {"OTHER_REG_NUMBER", type text}, {"HAIRCUT_CATEGORY", type text}, {"TYPE", type text}, {"REFERENCE_MARKET", type text}, {"DENOMINATION", type text}, {"ISSUANCE_DATE", type datetime}, {"MATURITY_DATE", type datetime}, {"ISSUER_CSD", type text}, {"COUPON_RATE (%)", type number}, {"ISSUER_NAME", type text}, {"ISSUER_RESIDENCE", type text}, {"ISSUER_GROUP", type text}, {"GUARANTOR_NAME", type text}, {"GUARANTOR_RESIDENCE", type text}, {"GUARANTOR_GROUP", type text}, {"COUPON_DEFINITION", type text}, {"HAIRCUT", type number}, {"HAIRCUT_OWN_USE", type number}, {"POTENTIALLY_OWN_USABLE_COVERED_BOND", type text}})
    in
        #"Changed Type"

    • Edited by Giovanni_fvg Tuesday, February 18, 2020 2:18 PM
    • Proposed as answer by Lz._ Wednesday, February 19, 2020 6:41 AM
    • Marked as answer by Giovanni_fvg Wednesday, February 19, 2020 10:53 AM
    Tuesday, February 18, 2020 1:36 PM

All replies

  • Hi Giovanni

    If you search this site you will find tens of similar threads… In a nutshell:

    1. Pass value as parameter to Power Query Get & Transform
    2. Assuming - within your query - you name your date variable myDateVar then construct your string as follow:

    "https://www.ecb.europa.eu/paym/coll/assets/html/dla/ea_MID/ea_csv_" & Text.From(Date.Year(myDateVar)) & Text.From(Date.Month(myDateVar)) & Text.From(Date.Day(myDateVar)) & ".csv"

    Hope this helps & if this solves the issue please Mark as answer (can help others) - Thanks + Nice day...

    • Proposed as answer by Lz._ Wednesday, February 19, 2020 6:41 AM
    Tuesday, February 18, 2020 12:45 PM
  • Hello,

    thanks for answer. It works fine now.

    I had read some articles before posting, but I had some issues. 

    I simplified a bit the task, added a Table with the date formatted as per URL, and amended in Advance Editor of the power query as follows:

    let
        myDateVar = Table.FirstValue(Excel.CurrentWorkbook(){[Name="Table3"]}[Content]),
        Source = Csv.Document(Web.Contents("https://www.ecb.europa.eu/paym/coll/assets/html/dla/ea_MID/ea_csv_" & myDateVar & ".csv"),[Delimiter=" ", Columns=20, Encoding=1200, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ISIN_CODE", type text}, {"OTHER_REG_NUMBER", type text}, {"HAIRCUT_CATEGORY", type text}, {"TYPE", type text}, {"REFERENCE_MARKET", type text}, {"DENOMINATION", type text}, {"ISSUANCE_DATE", type datetime}, {"MATURITY_DATE", type datetime}, {"ISSUER_CSD", type text}, {"COUPON_RATE (%)", type number}, {"ISSUER_NAME", type text}, {"ISSUER_RESIDENCE", type text}, {"ISSUER_GROUP", type text}, {"GUARANTOR_NAME", type text}, {"GUARANTOR_RESIDENCE", type text}, {"GUARANTOR_GROUP", type text}, {"COUPON_DEFINITION", type text}, {"HAIRCUT", type number}, {"HAIRCUT_OWN_USE", type number}, {"POTENTIALLY_OWN_USABLE_COVERED_BOND", type text}})
    in
        #"Changed Type"

    • Edited by Giovanni_fvg Tuesday, February 18, 2020 2:18 PM
    • Proposed as answer by Lz._ Wednesday, February 19, 2020 6:41 AM
    • Marked as answer by Giovanni_fvg Wednesday, February 19, 2020 10:53 AM
    Tuesday, February 18, 2020 1:36 PM
  • Hi Giovany

    Fine you marked your post as answer. However, could you edit it (I can't do that) and copy/paste at the top my points #1, #2 and the string construction so the solution is in a single post (can help others..). I'll then delete my posts

    Thanks

    Wednesday, February 19, 2020 6:43 PM