MAX Date

[AllanBerces]

HI can anyone help me on my PQ, on my table we required the max date on each Job no.

DESIRED OUTPUT

JOB No.	POD
XXXX2267	2/28/2026
XXXX2267	2/13/2026
XXXX2267	1/28/2026
XXXX2267	11/13/2025
XXXX2267	8/29/2025
TTTTT2269	2/28/2026
TTTTT2269	2/13/2026
TTTTT2269	1/28/2026
TTTTT2269	8/29/2025
YYY2281	4/3/2026
YYY2281	2/28/2026
YYY2281	2/13/2026
YYY2281	1/28/2026
YYY2281	12/23/2025
YYY2281	11/13/2025
YYY2281	6/27/2025
SVV2288	2/13/2026
SVV2288	1/28/2026
SVV2288	11/25/2025
RTE2290	11/13/2025
RTE2290	12/19/2025
RTE2290	11/17/2025
RTE2290	8/29/2025
QQQQ2879	11/17/2025
QQQQ2879	8/29/2025
KL2881	11/17/2025
KL2881	8/29/2025
WU2882	11/17/2025
WU2882	8/29/2025
QMK2242	2/13/2026
QMK2242	1/28/2026
QMK2242	11/21/2025
QMK2242	11/13/2025
QMK2242	6/27/2025
QMK2242	2/13/2026

[MasonMA · Accepted solution]

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"

[PhilipTreacy · Accepted solution]

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

[ronrsnfld · Accepted solution]

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"

[PhilipTreacy · Accepted solution]

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

[AllanBerces]

Hi @PhilipTreacy @MasonMA @ronrsnfld thank you very much for the reply work as i need

[MasonMA · Accepted solution]

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"