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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

7 REPLIES 7
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
Greg_Deckler
Super User
Super User

@ImkeF

@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors