MAX Date

Hi,

you can use the M code based on this logic.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZFLDoMwDETvkjVS8PBJcoCuaBdtKS1C3P8adaUK20nI8j2PB5ltcx9+wBhc4+ARPVqMbm8yQV1V0FmC6B8ZcsPzScT8e2xSUW+N7teGTjO2Z11XIBLz3qtVgm255lSdp5N54kVdrTe7iIjRIwh/LgvzWDQLt82Ksxhk0WO+AKktm5XginSWCDVhj3rnhxhSmVDGRqYrfyyVgYPb8feLMcrxg2cfdJuAHsXxhNvjKc6Caouy44mwv63avH8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"JOB No." = _t, POD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOB No.", type text}, {"POD", type date}}),
    #"Grouped" = Table.Group(#"Changed Type", {"JOB No."}, {{"Detail", each _, type table}}),
    #"Added Max POD" = Table.AddColumn(#"Grouped", "Max POD", each List.Max([Detail][POD])),
    #"Expanded Detail" = Table.ExpandTableColumn(#"Added Max POD", "Detail", {"POD"}, {"POD"}),
    #"Added POD_MaxOnly" = Table.AddColumn(#"Expanded Detail", "POD_MaxOnly", each if [POD] = [Max POD] then [POD] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added POD_MaxOnly",{"POD", "Max POD"})
in
    #"Removed Columns"

Hi AllanBerces 

Download PBIX file with the code shown below

Please note that I'm using dd/mm/yyyy in the images below but this code will work fine for you with mm/dd/yyyy.

To get exactly the output you asked for is actually quite tricky.  You could group the rows by Job No. and then extract the Max POD date but you will run into a couple of issues.

First for the job RTE2290, the max date will be on the 2nd row, but your desired result shows all the max dates on the first row for each job.:

You will also end up with 2 dates showing for the QMK2242 job because it has 2 entries that are the max date

To get the desired result you said you wanted, you need to first group by Job No. but I have then sorted the POD column in the table that results from that grouping.  So dates are sorted from most recent, for example for the QMK2242 job you get this

We only need the date from the first row, otherwsie you could end up with 13/02/2026 being shown twice.  So I blank all the dates in the other rows

= Table.TransformColumns(
    Grouped,
    {
        {"Detail",
            each
                let
                    t = Table.AddIndexColumn(_, "idx", 0, 1),
                    t2 = Table.AddColumn(t, "POD2", each if [idx] = 0 then [POD] else ""),
                    t3 = Table.RemoveColumns(t2, {"POD", "idx"}),
                    t4 = Table.RenameColumns(t3, {{"POD2", "POD"}})
                in
                    t4
        , type table}
    }
)

I can then delete the Job No. column and extract the data from the Detail column

Here's the full code which you can download from the link above.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZFLDoMwDETvkjVS8PBJcoCuaBdtKS1C3P8adaUK20nI8j2PB5ltcx9+wBhc4+ARPVqMbm8yQV1V0FmC6B8ZcsPzScT8e2xSUW+N7teGTjO2Z11XIBLz3qtVgm255lSdp5N54kVdrTe7iIjRIwh/LgvzWDQLt82Ksxhk0WO+AKktm5XginSWCDVhj3rnhxhSmVDGRqYrfyyVgYPb8feLMcrxg2cfdJuAHsXxhNvjKc6Caouy44mwv63avH8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"JOB No." = _t, POD = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"POD", type date}}, "en-US"),
    #"Grouped" = Table.Group(
        #"Changed Type with Locale",
        {"JOB No."},
        {{"Detail", each Table.Sort(_, {{"POD", Order.Descending}}), type table}}
    ),
    #"Added Custom" = Table.TransformColumns(
    Grouped,
    {
        {"Detail",
            each
                let
                    t = Table.AddIndexColumn(_, "idx", 0, 1),
                    t2 = Table.AddColumn(t, "POD2", each if [idx] = 0 then [POD] else ""),
                    t3 = Table.RemoveColumns(t2, {"POD", "idx"}),
                    t4 = Table.RenameColumns(t3, {{"POD2", "POD"}})
                in
                    t4
        , type table}
    }
),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"JOB No."}),
    #"Expanded Detail" = Table.ExpandTableColumn(#"Removed Columns", "Detail", {"JOB No.", "POD"}, {"JOB No.", "POD"})
in
    #"Expanded Detail"

Regards

Phil

Here's another method, adding a column to the JOB group where only the first entry has the Final POD:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZFLDoMwDETvkjVS8PBJcoCuaBdtKS1C3P8adaUK20nI8j2PB5ltcx9+wBhc4+ARPVqMbm8yQV1V0FmC6B8ZcsPzScT8e2xSUW+N7teGTjO2Z11XIBLz3qtVgm255lSdp5N54kVdrTe7iIjRIwh/LgvzWDQLt82Ksxhk0WO+AKktm5XginSWCDVhj3rnhxhSmVDGRqYrfyyVgYPb8feLMcrxg2cfdJuAHsXxhNvjKc6Caouy44mwv63avH8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"JOB No." = _t, POD = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOB No.", type text}, {"POD", type date}}),
    
    #"Group JOBs" = Table.Group(#"Changed Type","JOB No.",{
        {"Final POD", each {List.Max([POD])}&List.Repeat({null},Table.RowCount(_)-1), 
            type {nullable date}}
                }),
    
    #"Expanded Final POD" = Table.ExpandListColumn(#"Group JOBs", "Final POD")
in
    #"Expanded Final POD"