Create a date Variable in excel and get power query to repeat that date over all rows in a table

[Jimbo1980]

I have a table with approx 6,000 rows. Then I have a report date in excel i.e. the date I run the report e.g. 22/07/2024.  What I want to do is to refer to the date I run the report so I can perform calculations in the 6,000 rows. The report date is essentially a variable thats sits in excel and changes. I have tried a few things like importing the date as connection only, then adding a column with the reference for the report date variable but to no avail. 

Any advise is much appreciated. I am sure there must be a way but I cannot figure it out!

[PwerQueryKees]

I don't have access to my laptop at the moment. But ...

• Make a new empty query.
• Enter the formula Excel.CurrentWorkbook()
• This will show you all the accessible content 
• Find your named range
• Click on the value in the content column
• If it appears as a table, you may need to drill down until it just goes the date.

Give you query a name and you can use it in any expression...

[Jimbo1980]

so i have managed to add the report date as a query, I have drilled down. The reference for the date in power query appears to be '= #"Changed Type"{0}[Column1]'. But then if I go into another query how do i reference this report date query?

[PwerQueryKees]

Make a named range in Excel that refers to the report run date. You can access this named range inpiwerquery as a separate query. You may want to transform it a bit depending on how you want to use it. Assuming you transform it to produce just the actual date value, you can use the name of your query anywhere you want the report run date.

[Jimbo1980]

Hi thanks for your reply, 

So i have made a named range in excel called 'Report_Date' which refers to cell B2 the date (22/07/2024). But how do i refer to that variable in power query? 

[Anonymous]

Hi @Jimbo1980 ,

Thanks for reaching out to the community.

To get the current date in Power Query, you can use the expression “= DateTime.Date (DateTime.LocalNow ())” in the Formula Bar. This generates the current datetime value in Power Query by using the DateTime.LocalNow function, which looks at the current date and time on your system.

DateTime.LocalNow - PowerQuery M | Microsoft Learn

How to Create Today's Date in Power Query M (gorilla.bi)

Best Regards,

Stephen Tao

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

[Jimbo1980]

Hi Stephen, 

Thank you for your reply but unfortunately the current date isn't what I am looking for. I need to create a variable which I can type the report date in (This could be a date in the past). Then I can use this variable or object to refer to in my table of 6,000 rows to perform the necessary calculations.

[Anonymous · Accepted solution]

Hi @Jimbo1980 ,

After I reviewed again, I understand that you want call a column from another table.

Try adding a custom column. #"tablename"[columnname]. 

#"Table (2)"[ReportDate]

[RpoertDate] is in Table(2).

Expand it.

You can see the [ReportDate] from Table(2) now is in Table.

Similar post:Solved: How to reference another table in Query Editor (M) - Microsoft Fabric Community

Best Regards,

Stephen Tao

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

[Jimbo1980]

Thanks for that Stephen that worked!