Forum Discussion
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
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.
9 Replies
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.
Thanks! this was very helpfull.
Bas
Great! Glad it helped.
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.
I thought I'd share my solution to this issue.
letLatestDate = 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"
