Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
8 months ago
Solved

Filtering a table on the Max date by department

Hi -  I am trying to do the following (I posted something earlier this month and I think the example was incorrect). I would like to pull the max date based upon a specific department.   Input: D...
Need Help
Show and Tell
  • jerryr125's avatar
    jerryr125
    7 months ago

    Thank you everyone for examples and assistance - appreciate it.

    I ended up doing the following:

    - Sort by Department (Ascending) then by date (Decending - putting the most recent date first)

    - Adding ranking logic by Department

    - The result is the most recent date results in a ranking of 1 for each departmnt

    - Filter on the 1 Ranking

    As the data dynamically updates, I will always get the most recent date.

    Thanks - Jerry

jerryr125's avatar
jerryr125
Helper IV
8 months ago

Hi MasonMA 

Thank you again for your help.

Ok so my question is this - exactly where do I put that code inthe Power BI / Power Query workflow ?

I already have a lot of code in the workflow when I click on the 'advanced editor' - do I add the code below to the end? 

thanks - Jerry

MasonMA's avatar
MasonMA
Super User
8 months ago

Hi Jerry;

 

The one i provided and tested is a Power BI approach and you can use without involving Power Query. To add M code in Power Query/Dataflow, you would need to use ronrsnfld and others' Power Query solution.

 

For that, yes you would need to open the Query editor in Power Query and paste the code inside, 

 

"

 #"Changed Type" = Table.TransformColumnTypes(LastStep,{{"DEPARTMENT", type text}, {"KPIDATE", type date}, {"KPISCORE", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DEPARTMENT"}, {
        {"KPIDATE", (t)=> Table.SelectRows(t, each [KPIDATE]=List.Max(t[KPIDATE])),
            type table[DEPARTMENT=text, KPIDATE=date, KPISCORE=Int64.Type]}}),
    #"Expanded KPIDATE" = Table.ExpandTableColumn(#"Grouped Rows", "KPIDATE", {"KPIDATE", "KPISCORE"})
        
in
    #"Expanded KPIDATE"

"-- from ronrsnfld