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 advance

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

     

     

4 Replies

  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    May 16, 2022
    It looks like you want to get the rows with max createdon for each unique value of ced_unitname. Can you confirm that is what you want? If not, please could you clarify the goal?
    • 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