Replace a value in a cell by a value in a cell above conditionally

[Anonymous]

Hi,

I am trying to replace a specific value in a cell with an other value in a cell above, but it only works for 1 cell.

For exampe, with the table :

col1 | col2

A | 17

B | 13

s | 14

s | 15

s | 28

C | 24

s | 32

What I want is to replace the three first letters "s" with the letter "B" and the last one with the letter "C". This is just an example and I can have more than only 4 "s" to replace.

In my Advanced Editor, I tried with a customed column :

#"Added Index" = Table.AddIndexColumn(#"Step 2", "Index", 0, 1, Int64.Type),
#"Added values above" = Table.AddColumn(#"Added Index", "Custom", each if [Col2] = "s" then #"Added Index"[Col2]{[Index] - 1} else [Col2])

But as I thought, it only replaces the first row with the "s", and the others don't change

If you can help me to do that, I would be greatful 🙂

[Vera_33 · Accepted solution]

Hi @Anonymous 

Is it what you want?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRQ0lEyNFeK1YlWcgKzjcHsYjDbBIltimAbWYDZzmA2khpjI6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col1 = _t, col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col1", type text}, {"col2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","s ",null,Replacer.ReplaceValue,{"col1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"col1"})
in
    #"Filled Down"