Maximum Dates of each product in a list with multiple entries

Question

Hi,

I´m trying to get the maximum date of each product in a list. An example of the data with the correct result in the third row is as follows:

Any ideas? I´m familiar with excel, but still new to power query...

Thanks in advance!

Answer 1

Hi @LucasSalomons-0933

No worries at all. As you seem to be new on the forum: when a proposal solves your problem, it's a good habit to accept it as answer in order to help those who search before posting

Below is another option that is easier to understand for someone who begins with PQ where, inside Table.Group I decomposed the steps, hopefully with names that talk:

let  
    // Table for demo.  
    Source = Table.FromColumns(  
        {  
            List.Dates(#date(2020,7,2), 15, #duration(2,0,0,0)),  
            List.Repeat({"A"}, 4) & List.Repeat({"B"}, 6)  
                & List.Repeat({"C"}, 5)  
        },  
        type table [Date = date, Product = text]  
    ),  
    GroupedProduct = Table.Group(Source, {"Product"},  
        {"TBL", each  
            let  
                DatesAsList = Table.Column(_, "Date"),  
                MaxInList = List.Max(DatesAsList),  
                AddedColumn = Table.AddColumn(_, "Date Max", each  
                    MaxInList, type date  
                )  
            in  
                AddedColumn,  
            type table  
        }  
    ),  
    CombinedNestedTables = Table.Combine(GroupedProduct[TBL])  
in  
    CombinedNestedTables  

Any question let me know - not to explain every steps though :) as you're right a bit of home work is required

Just in case, PQ documentation is available here
Cheers

Answer 2

Hi @LucasSalomons-0933

One way:

let
    // Table for demo.
    Source = Table.FromColumns(
        {
            List.Dates(#date(2020,7,2), 15, #duration(2,0,0,0)),
            List.Repeat({"A"}, 4) & List.Repeat({"B"}, 6)
                & List.Repeat({"C"}, 5)
        },
        type table [Date = date, Product = text]
    ),
    GroupedProduct = Table.Group(Source, {"Product"},
        {"TBL", (state)=>
            Table.AddColumn(state, "Date Max", each
                Table.Max(state, {"Date"})[Date], type date
            ),
            type table
        }
    ),
    CombinedNestedTables = Table.Combine(GroupedProduct[TBL])
in
    CombinedNestedTables

If you're new to PQ, search the former forum (and this one), it's a huge source of information & solved cases
Also, if you need some "learning" pointers let me know