CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktop
chris_olv
chris_olv

Reputation: 122

Select the maximum day of every month in Power Query in Power BI

I have the following dataset in power BI :

enter image description here

I'd like to select for every month/year, ONLY the rows with highest day (and delete the rest).

In this example, the rows 4-7 would be deleted

Do you have any idea how to approach this problem ?

Thank you,

Best

Edit : month AND year

Upvotes: 0

Views: 437

Answers (1)

horseyride
horseyride

Reputation: 21373

You dont mention it, but don't you have to check year as well? Anyway, one way is to break it down to year, month, day, find the max day for the year/month combination, see if it matches the day on that row, then filter

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "YearMonth", each Date.Year([Date])*1000+Date.Month([Date])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"YearMonth"}, {{"data", each 
    let a = List.Max(_[Date]),
    #"Added Custom2" = Table.AddColumn(_, "Custom", (x)=>if x[Date]<> a then "XX" else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [Custom] = null)
    in #"Filtered Rows"
, type table [Date=nullable date]}}),
#"Expanded Data" = Table.ExpandTableColumn(Table.SelectColumns(#"Grouped Rows",{"data"}), "data", Table.ColumnNames(#"Changed Type"),Table.ColumnNames(#"Changed Type"))
in #"Expanded Data"

enter image description here

Upvotes: 1

Related Questions