Reference a date field in spreadsheet

[staincross]

Hi There,

I have multiple Excel Power Queries that use the same dates - example line:

= Table.SelectRows(#"Filtered Rows", each [Created On] < #datetime(2022, 1, 11, 0, 0, 0))

I would like the #datetime to reference a date field in my spreadsheet so I just update the one cell for all queries.

How can I go about referencing this please?

[v-angzheng-msft · Accepted solution]

Hi, @staincross 

Method1:

Create a blank query, open Advanced Editor and replace the text there with the code below.  

let
    Source = #datetime(2021, 10, 1, 0, 0, 0)
in
    Source

Rename the query name(date1) and modify the date you want in the advanced editor.

= Table.SelectRows(#"Changed Type", each [Date] > date1)

Method2:

Select the date of the cell you want, load the date into Power Query, and check with or without table header.
Then modify the loaded query, add the following code at the end of the query, and rename the query name.

{0}[Rdatetime]
// or
{0}[Column1]

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

You can load cells into Power Query if they are a named range like this:

Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

See an example here:

https://stackoverflow.com/questions/49281763/powerquery-use-a-cell-values-in-a-url

[staincross]

Thanks.  I'm sure that's a good answer for a lot of people who know their way around, but unfortunately, I'm pretty green and don't really get it.

[AlexisOlson]

If you have specific questions, then ask. I can't tell which part you don't get.