none
How to get most recent rows RRS feed

  • Question

  • Hi,

    I have a List of brands to be published by Sales Rep on a daily basis. The below pic shows the sample data.

     

    I need to know who published each brand most recently.. Something like

     

    I tried group by brand name and max(Published) but I will not get the unique Published_By_Id.. 

    How do I get this done?

    Thursday, December 3, 2015 12:11 PM

Answers

  • For a better comparison of the two solutions, I refactored my script to Bill's nested coding style and removed a couple of steps that were not absolutely necessary. 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"Published", type datetime},{"Brand_Name", type text},{"Published_By_Id", Int64.Type}}),
        Group = Table.Group(ChType, {"Brand_Name"}, {{"Table", each _, type table}}),
        Removed = Table.RemoveColumns(Table.TransformColumns(Group, {"Table", each Table.Max(_,"Published")}),"Brand_Name"),
        ExpandedTable = Table.ExpandRecordColumn(Removed, "Table", {"Brand_Name", "Published", "Published_By_Id"})
    in
        ExpandedTable

    Each script provides the same solution in the same number of steps (no idea of any performance differences with large datasets). The main difference is that Bill uses sort descending to get latest date and I use a Max function on the date field to achieve the same goal.

    • Marked as answer by Olivia127 Thursday, December 10, 2015 4:55 AM
    Friday, December 4, 2015 4:54 AM

All replies

  • Try the following script:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        ChangedColumnnTypes = Table.TransformColumnTypes(Source,{{"Published", type datetime},
                                                 {"Brand_Name", type text}, {"Published_By_Id", Int64.Type}}),
        GroupedByBrand = Table.Group(ChangedColumnnTypes, {"Brand_Name"},
                                        {{"Table", each _, type table}}),
        TableMaxDate = Table.TransformColumns(GroupedByBrand, {"Table", each Table.Max(_,"Published")}),
        RemovedBrandNameColumn = Table.RemoveColumns(TableMaxDate,"Brand_Name"),
        ExpandedTable = Table.ExpandRecordColumn(RemovedBrandNameColumn, "Table",
                                      {"Published", "Brand_Name", "Published_By_Id"}),
        ChangedDateTimeType = Table.TransformColumnTypes(ExpandedTable,{{"Published", type  datetime}}), //From type "any" to "datetime"
        ReorderedColumns = Table.ReorderColumns(ChangedDateTimeType,{"Brand_Name", "Published", "Published_By_Id"})
    in
        ReorderedColumns

    Remember to substitute "Source" with your actual source


    Thursday, December 3, 2015 6:59 PM
  • Hi Olivia :-)Slightly different approach:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"Published", type datetime}, {"Brand_Name", type text}, {"Published_By_Id", Int64.Type}}),
        Group = Table.Group(ChType, {"Brand_Name"}, {{"tbl", each Table.Sort(_,{{"Published", Order.Descending}}){0}[[Published],[Published_By_Id]], type record}}),
        ExpandRecord = Table.ExpandRecordColumn(Group, "tbl", {"Published", "Published_By_Id"}, {"Published", "Published_By_Id"}),
        SortBrand = Table.Sort(ExpandRecord,{{"Brand_Name", Order.Ascending}})
    in
        SortBrand

    Thursday, December 3, 2015 9:28 PM
  • For a better comparison of the two solutions, I refactored my script to Bill's nested coding style and removed a couple of steps that were not absolutely necessary. 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"Published", type datetime},{"Brand_Name", type text},{"Published_By_Id", Int64.Type}}),
        Group = Table.Group(ChType, {"Brand_Name"}, {{"Table", each _, type table}}),
        Removed = Table.RemoveColumns(Table.TransformColumns(Group, {"Table", each Table.Max(_,"Published")}),"Brand_Name"),
        ExpandedTable = Table.ExpandRecordColumn(Removed, "Table", {"Brand_Name", "Published", "Published_By_Id"})
    in
        ExpandedTable

    Each script provides the same solution in the same number of steps (no idea of any performance differences with large datasets). The main difference is that Bill uses sort descending to get latest date and I use a Max function on the date field to achieve the same goal.

    • Marked as answer by Olivia127 Thursday, December 10, 2015 4:55 AM
    Friday, December 4, 2015 4:54 AM