Concatenate one column per ID

Question

I have a table with multiple IDs and each ID is repeated with another column [Stage] values.IDStage1234Sent1234Created1234Waiting1234Waiting9874Waiting9874Sent6542Offer6542Waiting6542SentI want to create a column that is a concatenation of stages that occur per ID - but I only care about "Sent" and "Waiting" and "Offer". If other stages occur, leave the custom column blankIDStage1234Sent, Waiting1234&nbsp;1234Sent, Waiting1234Sent, Waiting9874Sent, Waiting9874Sent, Waiting6542Sent, Waiting, Offer6542Sent, Waiting, Offer6542Sent, Waiting, Offer

lbendlin · Accepted Answer

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMjQyNlHSUQpOzStRitWB852LUhNLUlOQhcITM0sy89JxCVlamOMQgpttZmpiBOT7p6WlFiELIOuBCkH0xAIA", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [ID = _t, Stage = _t]
  ), 
  #"Replaced Value" = Table.ReplaceValue(
    Source, 
    each [Stage], 
    (k) =&gt;
      let
        ld = List.Distinct(
          Table.SelectRows(
            Source, 
            each [ID] = k[ID] and List.Contains({"Sent", "Waiting", "Offer"}, [Stage])
          )[Stage]
        )
      in
        if List.Contains(ld, k[Stage]) then Text.Combine(ld, ", ") else null, 
    Replacer.ReplaceValue, 
    {"Stage"}
  )
in
  #"Replaced Value"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Forum Discussion

Related content

Concatenate 3 columns

create custom column concatenated

Power query - concatenate text columns

Concatenate lists ;

Concatenate multiple columns to a single column