Forum Discussion

PowerBeeEye's avatar
PowerBeeEye
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

Find row with latest date and keep one

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...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
PowerBeeEye
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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