Forum Discussion
Solved
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 aft...
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.
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"