Forum Discussion

hussein_elsayed's avatar
hussein_elsayed
Brass Contributor
May 16, 2022
Solved

get the last record data according to the creation date

Dears, Please need your assist to get the last record from a huge data base based on the creation date.   N.B : attached sample of data and the return value that i want to show.   Thanks in adva...
Formulas and Functions
  • flexyourdata's avatar
    flexyourdata
    May 16, 2022

    hussein_elsayed 

     

    I believe since you have multiple columns that define uniqueness, it would be best to use PowerQuery to get the results you want.

     

    You can create a blank query using Data>Get & Transform Data>Get Data>From Other Sources>Blank Query and put this query in the Advanced Editor:

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    change_types = Table.TransformColumnTypes(Source,{{"ced_clientresponsename", type text}, {"ced_clientresponsedate", type date}, {"createdon", type date}, {"ced_checkamount", type number}, {"ced_checkbankname", type text}, {"ced_checkdate", type date}, {"ced_checknumber", Int64.Type}, {"ced_unitname", type text}, {"ced_typename", type text}, {"ced_name", type text}}),
    groups = Table.Group(change_types, {"ced_checkamount", "ced_checkdate", "ced_checknumber", "ced_unitname", "ced_name"}, {{"max_createdon", each List.Max([createdon]), type nullable date}}),
    merge = Table.NestedJoin(change_types, {"ced_checkamount", "ced_checkdate", "ced_checknumber", "ced_unitname", "ced_name", "createdon"}, groups, {"ced_checkamount", "ced_checkdate", "ced_checknumber", "ced_unitname", "ced_name", "max_createdon"}, "Groups", JoinKind.Inner),
    remove_col = Table.RemoveColumns(merge,{"Groups"})
in
    remove_col

     

     

hussein_elsayed's avatar
hussein_elsayed
Brass Contributor
May 16, 2022
Yes max createdon ,the criteria will aply for all uniq rows based on the data in the Culumn D & F & G & H & J.
Please recheck the second sheet that including the data what i want to return
flexyourdata's avatar
flexyourdata
Iron Contributor
May 16, 2022

hussein_elsayed 

 

I believe since you have multiple columns that define uniqueness, it would be best to use PowerQuery to get the results you want.

 

You can create a blank query using Data>Get & Transform Data>Get Data>From Other Sources>Blank Query and put this query in the Advanced Editor:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    change_types = Table.TransformColumnTypes(Source,{{"ced_clientresponsename", type text}, {"ced_clientresponsedate", type date}, {"createdon", type date}, {"ced_checkamount", type number}, {"ced_checkbankname", type text}, {"ced_checkdate", type date}, {"ced_checknumber", Int64.Type}, {"ced_unitname", type text}, {"ced_typename", type text}, {"ced_name", type text}}),
    groups = Table.Group(change_types, {"ced_checkamount", "ced_checkdate", "ced_checknumber", "ced_unitname", "ced_name"}, {{"max_createdon", each List.Max([createdon]), type nullable date}}),
    merge = Table.NestedJoin(change_types, {"ced_checkamount", "ced_checkdate", "ced_checknumber", "ced_unitname", "ced_name", "createdon"}, groups, {"ced_checkamount", "ced_checkdate", "ced_checknumber", "ced_unitname", "ced_name", "max_createdon"}, "Groups", JoinKind.Inner),
    remove_col = Table.RemoveColumns(merge,{"Groups"})
in
    remove_col

 

 

Resources