Find row with latest date and keep one

[PowerBeeEye]

Form a table, I am trying to pick the each ForestDNSName

Scan Date	ForestDNSName	Column1
2020-09-15	Fabrikam.com
2020-09-05	Contoso.com
2020-09-15	Contoso.com
2020-09-15	Contoso.com

Using the following lines:

    Row_Group_by_ForestDNSName = Table.Group(Row_Filter, {"ForestDNSName"}, {{"Count", each _, type table}}),
    Extract_Latest_Data = (TT as table) =>
  Table.SelectRows(TT, each [Scan Date] = List.Max(TT[Scan Date])),
    Custom_Column_LatestData = Table.AddColumn(Row_Group_by_ForestDNSName, "LatestData", each Extract_Latest_Data([Count])),
    Column_Remove_Except_LatestData = Table.RemoveColumns(Custom_Column_LatestData,{"ForestDNSName", "Count"}),

The problem arises when there are multiple rows with the same (latest) Scan Date.

How can I edit the lines to pick the top row in the resulting table?

Scan Date	ForestDNSName	Column1
2020-09-15	Fabrikam.com
2020-09-15	Contoso.com

[Jimmy801]

Hello @PowerBeeEye 

the easiest and shortest way to do is to put all what you needed in the Group-function and expand it later

here the code snipped for the function of the group function (you can substitute with your function, and it will work). No need to create an additional column to extract etc.

        {
            {
                "PickUpLatest", 
                (tbl)=> Table.FirstN(Table.SelectRows(tbl, (row)=> row[Scan Date]= List.Max(tbl[Scan Date])),1)
            }
        }

here a complete code example to see how the above function is working

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1sNQ1NFXSUXJLTCrKzE7M1UvOzwVyFZRidRAqDEAqnPPzSvKL87EqMCRPQSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Scan Date" = _t, ForestDNSName = _t, Column1 = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Scan Date", type date, "en-US"}, {"ForestDNSName", type text}, {"Column1", type text}}),
    GroupAndPickUpLatest = Table.Group
    (
        ChangeType, 
        {"ForestDNSName"}, 
        {
            {
                "PickUpLatest", 
                (tbl)=> Table.FirstN(Table.SelectRows(tbl, (row)=> row[Scan Date]= List.Max(tbl[Scan Date])),1)
            }
        }
    ),
    PickUpLatest = Table.ExpandTableColumn(GroupAndPickUpLatest, "PickUpLatest", {"Scan Date", "Column1"}, {"Scan Date", "Column1"})
in
    PickUpLatest

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[AlB]

Hi @PowerBeeEye 

Try Table.FirstN to keep the first row only:

Table.FirstN(TableName , 1)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[PowerBeeEye]

@AlB  I'm very much a beginner when **bleep** comes to this, how can I add 

Table.FirstN(TableName , 1)

 to either of the following lines?

    Extract_Latest_Data = (TT as table) =>
  Table.SelectRows(TT, each [Scan Date] = List.Max(TT[Scan Date])),

OR

    Custom_Column_LatestData = Table.AddColumn(Row_Group_by_ForestDNSName, "LatestData", each Extract_Latest_Data([Count])),

Thanks!

[AlB · Accepted solution]

@PowerBeeEye 

Continuing on the code you had above:

    Row_Group_by_ForestDNSName = Table.Group(Row_Filter, {"ForestDNSName"}, {{"Count", each _, type table}}),
    Extract_Latest_Data = (TT as table) =>
  Table.SelectRows(TT, each [Scan Date] = List.Max(TT[Scan Date])),
    Custom_Column_LatestData = Table.AddColumn(Row_Group_by_ForestDNSName, "LatestData", each Extract_Latest_Data([Count])),
    Column_Remove_Except_LatestData = Table.RemoveColumns(Custom_Column_LatestData,{"ForestDNSName", "Count"}),

    #"Added Custom" = Table.AddColumn(Column_Remove_Except_LatestData, "Custom", each Table.FirstN([LatestData],1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Scan Date", "ForestDNSName"}, {"Scan Date", "ForestDNSName"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"LatestData"})

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers