Forum Discussion
Reference a date field in spreadsheet
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?
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 SourceRename 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 ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
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
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.
If you have specific questions, then ask. I can't tell which part you don't get.
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 SourceRename 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 ZhengIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
