CODEWITHSUNDEEP
powerbipowerquery
unstuck
unstuck

Reputation: 638

Filter a column in Power Query so that it contains only the last date of each year

Can anyone please advise on how to filter this column in Power Query so that it contains only the last date of each year?

enter image description here

So, this should contain only 3 rows:

31/12/2019
31/12/2020
31/03/2021

Upvotes: 0

Views: 2603

Answers (2)

Rick de Groot - gorilla.bi
Rick de Groot - gorilla.bi

Reputation: 416

This is an older thread, but for those who are looking for the easiest solution, you can use :

= Table.SelectRows(#"Converted to Table", each [Date] = Date.EndOfYear( [Date] ) )

If you want to see the entire code in action, you can paste this in the advanced editor:

let
Source = List.Dates( #date( 2010, 1, 1), Duration.Days( #date( 2022, 12, 31) - #date( 2010, 1, 1 ) ) + 1 , #duration(1,0,0,0) ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), type table[ Date = Date.Type ] , null, ExtraValues.Error),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each [Date] = Date.EndOfYear( [Date] ))
in 
#"Filtered Rows"

Hope that helps!

Rick de Groot

https://gorilla.bi

Upvotes: 0

horseyride
horseyride

Reputation: 21318

Try this

Add custom column to pull out the year

= Date.Year([EndDate])

Add custom column to pull out the max date for each matching year

= (i)=>List.Max(Table.SelectRows(#"Added Custom" , each [Year]=i[Year]) [EndDate])

Add custom column to check the two dates against each other

 = if [EndDate]=[MaxDate] then "keep" else "remove"

Filter on that column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EndDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([EndDate])),
#"Added Custom2" = Table.AddColumn(#"Added Custom","MaxDate",(i)=>List.Max(Table.SelectRows(#"Added Custom" , each [Year]=i[Year]) [EndDate]), type date ),
#"Added Custom1" = Table.AddColumn(#"Added Custom2", "Custom", each if [EndDate]=[MaxDate] then "keep" else "remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = "keep"))
in  #"Filtered Rows"

~ ~ ~

another way probably better for larger lists

Add custom column to pull out the year

= Date.Year([EndDate])

Group on year and take the Maximum of the EndDate Column

Merge that back to original data with left outer join and filter

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"EndDate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([EndDate])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Year"}, {{"MaxDate", each List.Max([EndDate]), type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type",{"EndDate"},  #"Grouped Rows"  ,{"MaxDate"},"Table2",JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"MaxDate"}, {"MaxDate"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([MaxDate] <> null))
in  #"Filtered Rows"

Upvotes: 2

Related Questions