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 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?