Forum Discussion
Order sequentially after Table.Buffer
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.
Anonymous- 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.
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.
Hi Anonymous - thank you for taking the time to come back to me, it's appreciated.
Your response was 100% accurate and worked perfectly