Forum Discussion

AI14's avatar
AI14
Helper III
3 years ago
Solved

Dynamically Change date range of web source (api link)

Im new to powerquery and have made a connection to a site using APi key. im trying to avoid going into the source every day to look at yesterdays data or any previous day. how can i do this dynamic...
Need Help
Show and Tell
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    AI14You need to include to and from in double quotes .... & "from=" & DateParameter & "to=" & DateParameter & ....

AntrikshSharma's avatar
AntrikshSharma
Community Champion
3 years ago

AI14 Are you using Excel for this? Then you can create a new query from a particular cell and then reference that query in the above code.

AI14's avatar
AI14
Helper III
3 years ago

yes i am, sorry i didnt mention it.

 

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    AI14 Create a table in Excel with name DateParameter

    And then you can load the table into PQ, the code will look like this:

    let
    Source = 
        Excel.CurrentWorkbook(){[ Name = "DateParameter" ]}[Content],
    ChangedType = 
        Table.TransformColumnTypes ( Source, { { "Date", type datetime } } ),
    Date = 
        Date.ToText (
            Date.From ( ChangedType[Date]{0} ),
            [ Format = "yyyy-MM-dd", Culture = "en-US" ]
        )
in
    Date

    As you can see the the data type of quer is just scalar text value now you can use the name DateParameter in your original code.

    • AI14's avatar
      AI14
      Helper III
      3 years ago

      ammazing and would this work for both from & to dates?