cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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.

staincross_0-1642023042420.png

 

How can I go about referencing this please?

1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

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.

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

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.

Syndicate_Admin
Administrator
Administrator

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.

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,334)