List Fill Down in Power Query - 7 Methods:

Today, I will demonstrate seven methods in Power Query to fill down lists.

ListFillDown.xlsx

Download XLSX • 17KB

Source = {"Mike", null, null, "Nabil", "Leila","Rick",null, "Alan"}

Method-1:

Result = List.Accumulate(Source, {}, (s,c) => s & {c ?? List.Last(s)})

Method-2:

Result = Table.FillDown(Table.FromValue(Source),{"Value"})[Value]

Method-3:

Result = List.Accumulate(Source, {} , (s,c) => s & {c ?? List.Last(List.FirstN(s, each _ <> null))})

Method-4:

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

Method-5:

Result = List.Transform({0..List.Count(Source) -1}, (f) => Source{f} ?? List.Last(List.RemoveNulls(List.Range(Source,0,f))))

Method-6:

Result = List.Accumulate(List.PositionOf(Source, null, 2), Source, (s,c) => List.ReplaceRange(s,c,1, {List.Last(List.RemoveNulls(List.Range(Source,0,c+1)))}))

Method-7 suggested by Sergei Baklan:

let

 Source = {"Mike", null, null, "Nabil", "Leila","Rick",null, "Alan"},

 n = List.Count(Source)-1,

 fn = (list, k) =>

 [

 a = if list{k} = null then List.ReplaceRange(list, k, 1, {list{k-1}}) else list,

 return = if k=n then a else @fn(a, k+1)

 ][return]

in

 fn(Source,0)