Adding values to categories

Hi Dimitry1 ,

Multi-select (Ctrl+click) your [Ship] and [Date] columns.

Go to the Home tab > Group By.

Replace the default 'Count' aggregated column with an 'All Rows' column called 'data':

Once grouped, add a new custom column like this:

List.Max([data][Duty status])

Then expand your original columns back out from the nested data column:

To end up with this:

Full example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCk7NBJJeieXFQMrIwMhI11DXwFApVidayT9PwaW0pBKuKDgvsaAgtQhDHVyBW04mpoJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Duty status" = _t, Ship = _t, #"Fish name" = _t, Date = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Duty status", type text}, {"Ship", type text}, {"Fish name", type text}, {"Date", type date}}),

    groupShipDate = Table.Group(chgTypes, {"Ship", "Date"}, {{"data", each _, type table [Duty status=nullable text, Ship=nullable text, Fish name=nullable text, Date=nullable date]}}),
    addMaxDutyStatus = Table.AddColumn(groupShipDate, "maxDutyStatus", each List.Max([data][Duty status])),
    expandDataCol = Table.ExpandTableColumn(addMaxDutyStatus, "data", {"Duty status", "Fish name"}, {"Duty status", "Fish name"})
in
    expandDataCol

Pete