Power Query max date from other table

[Basbitter]

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

[DataSkills]

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]

Thanks! this was very helpfull. 

Bas

[edhans]

Great! Glad it helped.

[michellepace]

@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.

[michellepace]

@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? 

[edhans]

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. 

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

[edhans · Accepted solution]

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.

[msahari]

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]

@ImkeF