Adding values to categories

[Dimitry1]

I have a data where only one row from a report has a value "On duty" or "Off duty" in a Duty Status column. The data can be grouped by Ship and Date columns.  My task for today would be to fill all rows in "Duty status" column while the value is only in a one row. Like.... if Certain "Date" and "Ship" has "On duty" value in "Duty Status" column then all rows should be filled with "On duty" values on this Date and Ship. In other words, now in  a "Fish nomenclature" only "Missed/ needless names" values has "On duty/ Off duty" values and I need to get Duty Status for all Fish Nomenclature rows. Column from an example can not do it. I think maybe the data could be grouped by Date and Ships and then get a Duty status value.

[BA_Pete · Accepted solution]

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

[Dimitry1]

Thanks alot, Pete! Solved! By the way, I'm doing my first analysis and it was my first question. Now I know where to find quick help.