Order sequentially after Table.Buffer

[Tob_P]

I have the following in PQ...

let
Source = #"NAV_Sales Header",
#"OTP <> Blank" = Table.SelectRows(Source, each ([OTP No_] <> "")),
#"Sorted Created On Desc/added Table.Buffer prefix" = Table.Buffer(Table.Sort(#"OTP <> Blank",{{"Created on Date", Order.Descending}})),
#"Filtered to CQ's only" = Table.SelectRows(#"Sorted Created On Desc/added Table.Buffer prefix", each Text.StartsWith([No_], "CQ")),
#"Removed Duplicate OTPs" = Table.Distinct(#"Filtered to CQ's only", {"OTP No_"}),
#"Sorted No_ Desc" = Table.Sort(#"Removed Duplicate OTPs",{{"No_", Order.Descending}})
in
#"Sorted No_ Desc"

This was taken from an article online (essentially advising all the steps but adding in Table.Buffer as a prefix to Table.Sort to achieve the desired result) whereby I wanted to find the latest Created On Date for each No_ field - essentially it strips out everything else and leaves one value for the No_ field (when there could be multiple). This works well mostly but I have instances where there can be multiple No_'s on the same date. I don't have the luxury of a time stamp, but what I'm finding is that it returns the first sequential number in the list and I would like it to return the last sequential number in the No_ field.

Can anyone advise how I could amend the above to achieve the latest (or highest) sequential No_ based on the Created on Date? No_ btw is a text field as it follows the following format TTDDDDDD

[v-xinruzhu-msft]

Hi @Tob_P 

You can use group by, then select group by date, you can refer to the following picture.

And then expand the data column.

Output

And  you can refer to the following code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE31DdU0lEKCTE0MlaK1SFG0MTUDCFoBFNpaIhF0MgUU9DA0EgpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, No = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"No", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"No", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Date"}, {{"MaxNo", each List.Max([No]), type nullable text}, {"Data", each _, type table [Date=nullable date, No=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"No"}, {"No"})
in
    #"Expanded Data"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tob_P]

@v-xinruzhu-msft- Thank you for your reply.

Apologies, I haven't described this very well. I've taken your data and amended it to show the data and expected result...

In this example, I want to take the No value (which could have one or multiple Quote No's against it), and return the following:

The most recent Quote No (based on the date) and if there are multiple Quote No's on the most recent date, then return only the highest sequential number from the Quote No.

Ideally the table will just include that data eg. remove all rows that do not fit the criteria above.

[v-xinruzhu-msft · Accepted solution]

Hi @Tob_P 

You can refer to the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hcwxDoAgEETRu2xNAjOyu7H2BCZ2hPtfQwrRGIhWU7z8KUWYmCMiJMhxgEvbbQezmksNH+5p9Fbdrvo4ew90N7MfXydO7e7uoyfwcnWd/L+89fUE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, No = _t, #"Quote No" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"No", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"No", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"No"}, {{"MaxQuoteNo", each List.Max([Quote No]), type nullable text}, {"Data", each _, type table [Date=nullable date, No=nullable text, Quote No=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Quote No"}, {"Date", "Quote No"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each [Quote No] =[MaxQuoteNo]),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MaxQuoteNo"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tob_P]

Hi @v-xinruzhu-msft - thank you for taking the time to come back to me, it's appreciated.

Your response was 100% accurate and worked perfectly