Replace "0" value with another one if available

[JBDelvaux]

Greetings, I have a table as such. These informations are received to the database but from different devices that do not have access ot the same informations. As a result, one of the device cannot accurately tell which user did the job, leaving it to a UserId of 0.

I would like, if a line with a userID value is at "0" to check if another line with the same "Piece" number exist with a value different than 0 and to replace said 0 with the UserID found. If no other number is found, to keep it at 0.

[m_dekorte · Accepted solution]

You can try something like this. Note I'm using a simple example.

Do "Group By" on the Piece column, Choose "All Rows" and transform the result, like so.

let
  Source = Table.FromColumns(
    {
      {"A" .. "H"}, 
      {123 .. 127, 0, 0, 128}, 
      {546038 .. 546042, 546042, 546042, 546043}
    }, 
      {"Column1", "UserId", "Piece"}
  ), 
  GroupRows = Table.Group(
    Source, 
    {"Piece"}, 
    {{"t", each Table.TransformColumns(_, {"UserId", (x) => List.Max(_[UserId])})}}
  ), 
  ExpandAll = Table.Combine(GroupRows[t])
in
  ExpandAll

I hope this is helpful