Forum Discussion

jerryr0125's avatar
jerryr0125
New Member
9 months ago
Solved

Power Query - Keep Rows based upon maximum date?

Hi - I know there is a command in Power Query to 'keep rows', but is there a way to keep specific rows based upon the maximum date in the table ? Example - transactiontable - start   Date Tra...
Show and Tell
Tips and Tricks
  • Nasif_Azam's avatar
    Nasif_Azam
    9 months ago

    Hey jerryr0125 ,

     

    Use the following M code that should full fill your requirements:

    let
    Source = transactiontable,
    MaxDate = List.Max(Source[Date]),
    FilteredRows = Table.SelectRows(Source, each [Date] = MaxDate)
    in
    FilteredRows  

     

    Result:

     

    Also check the attached pbix file.

     

     

    Best Regards,
    Nasif Azam

     

MasonMA's avatar
MasonMA
Super User
9 months ago

Hi,

 

if you wanted to tweak your M code in Query Editor, use below M code so that it gives you the result you expected.

 

let
// previous steps...
#"LastStep" = ...,

// add these two lines:
MaxDate = List.Max(#"LastStep"[Date]),
#"MaxDateRows" = Table.SelectRows(#"LastStep", each [Date] = MaxDate)
in
#"MaxDateRows"