Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Basbitter
New Member

Power Query max date from other table

Hi All,

 

What I want is to create a paramater in PowerQuery with the max date of a certain column in one of the powerQuerytables. This way I can use that parameter to filter out only the dates after that max date.

 

What I wanted to do in PowerQuery is:

 

- Import data via one query in table "events". This table has the datetime column "created" ;

- Create a quey that shows Max "Created" from table "Events";

- Use that query as a parameter.

 

As I am relatively new can someone help me out?

 

Thanks!

 

Other solutions are welcome to!

 

Bas

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Hi, I do exactly this in creating date tables in Power Query. Basically you create a reference to your original table, click on the Date column, then in the Transform tab, select the Date dropdown and select Earliest or Latest. It creates a scalar value.

 

You cannot use it as a parameter as a parameter dropdown, but if you create a filter, just tell the filter to day "date > Jan 1, 2020" then after the filter is created, change the #date(2020,1,1) to varStartdate (or whatever you called the value.

 

See my full article on the date table here, which has info on how to create these dynamic values to use in other places.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

9 REPLIES 9
DataSkills
Resolver I
Resolver I

I thought I'd share my solution to this issue. 

 

let
  LatestDate = List.Max(#"Event"[Created]),
  Source = AnotherTable,
  #"Removed other columns" = Table.SelectColumns(Source, {"End of month"}),
  #"Removed duplicates" = Table.Distinct(#"Removed other columns", {"End of month"}),
  #"Added custom" = Table.AddColumn(#"Removed duplicates", "DisplayMonth", each if [End of month] = LatestDate then "Latest Month" else [End of month])
in
  #"Added custom"

Basbitter
New Member

Thanks! this was very helpfull. 

 

Bas

Great! Glad it helped.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans , thank you for such a great blog post (Create a dynamic date table in Power Bi). I have long wanted to tackle this inconvenient problem. Thank you very much.

 

@edhans , just a question. In your post you mention:

 

Make sure automatic date logic is off in Power BI. Select File, Options and Settings, Options, Data Load. Uncheck “Auto Date/Time”. Leaving this checked will create all sorts of unnecessary hidden tables and columns. You’ve created a perfect Date table. Don’t let Power BI’s AI mess this up.

 

So can I safely assume if I have created a "perfect" Date table, which all my other tables who have dates are connected to in a relationship, then I can always switch this off? 

Yes @michellepace .

You should always switch it off. I have my default to never have automatic date/time settings. Reasons:

  1. It creates a date table for every date column in your model. That is just a waste of space.
  2. It creates dates from the earliest to latest date. It should be from Jan 1 (or start of your year) to Dec 31 (or end of year).
  3. It creates dates on useless date fields. In the WorldWideImporters sample database from MS for example, there are at least 3 fields that have dates of Dec 31, 9999. This is because it is a product or program expiration date, and there is no mechanism to say "this program doesn't expire" so an impossible future date is entered. This creates a date table for each of these absurd fields with 2.9M rows each. I have seen this in the real world and just talked to user that sees this in their SAP databases.
  4. The date tables are super limited. Date, year, quarter, month. Not much else. No way to extend them, so you have to create your own anyway.
  5. SQLBI recommends in all of their books that reference Time Intelligence to turn this feature off. edhans_0-1600358535620.png

     

I have never ever seen anyone give a compelling argument for using it vs a real date table. Ever.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

Hi, I do exactly this in creating date tables in Power Query. Basically you create a reference to your original table, click on the Date column, then in the Transform tab, select the Date dropdown and select Earliest or Latest. It creates a scalar value.

 

You cannot use it as a parameter as a parameter dropdown, but if you create a filter, just tell the filter to day "date > Jan 1, 2020" then after the filter is created, change the #date(2020,1,1) to varStartdate (or whatever you called the value.

 

See my full article on the date table here, which has info on how to create these dynamic values to use in other places.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Spoiler
It's good but actually i wan to filter one table based on max of date in other table. using your method i have to change value of var every week, which eventually i don't want so.
Greg_Deckler
Community Champion
Community Champion

@ImkeF


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.