Replace consecutive repetitions with null in Power Query

In a previous post, we explored various techniques for filling down lists in Power Query. However, if the goal is to replace consecutive repetitions of a name with null values—that is, to do the reverse of fill down—this requires a different approach.

In this post, I will illustrate 4 methods to complete our task.

List = {"Mike","Mike","Mike","Nabil","Leila","Rick","Rick","Alan"}

Replace repetitions with null.xlsx

Download XLSX • 15KB

Method 1:

let

 Source = List,

 fx = (x as list, y as list) as list => [A = if List.Contains(y, x{0}) then {null} & @fx(List.Skip(x),y) else {x{0}} & @fx(List.Skip(x), y & {x{0}}), B = if x = {} then {} else A][B],

 Result = fx(Source,{})

in

 Result

Method 2:

let

 Source = List,

 Result = List.Accumulate(Source, {}, (s,c) => s & (if List.Last(List.RemoveNulls(s)) <> c then {c} else {null}))

in

 Result

Method 3:

let

 Source = List,

 Result = List.Generate(() => [a = Source{n}, n = 0], each [n] < List.Count(Source), each [a = if Source{n} = Source{[n]} then null else Source{n},n = [n] + 1], each [a])

in

 Result

Method 4:

let

 Source = List,

 Result = List.Transform( List.Positions(Source), (f) =>if List.Contains(List.Range(Source,0,f), Source{f}) then null else Source{f})

in

 Result