Home » Average Fill for Blank Months

Average Fill for Blank Months

Fill in all empty values with average of contiguous non-empty values on immediate left. Hence, for Company A, Mar would be average of Jan and Feb. Jun would be average of Apr and May (Mar was empty, hence contiguous non-empty values are Apr and May on immediate left) For C, Jan would be empty as there is no non-empty on immediate left. For May, it would be average of Feb, Mar and Apr

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 120
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Average Fill for Blank Months with Power Query

Power Query solution 1 for Average Fill for Blank Months, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.FromRows(
    List.Transform(
      Table.ToRows(Source), 
      each List.Transform(
        List.Positions(_), 
        (n) =>
          if _{n} <> null then
            _{n}
          else
            List.Last(
              List.RemoveNulls(
                Table.Group(
                  Table.FromValue(List.FirstN(List.Skip(_), n)), 
                  "Value", 
                  {"A", each Number.Round(List.Average([Value]))}, 
                  0, 
                  (b, e) => Number.From(e = null)
                )[A]
              )
            )
      )
    ), 
    Table.ColumnNames(Source)
  )
in
  Ans
Power Query solution 2 for Average Fill for Blank Months, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  C = each _ is null, 
  K = List.Skip, 
  S = Table.FromRows(
    List.Transform(
      Table.ToRows(Source), 
      each 
        let
          r = List.Reverse(K(_)), 
          s = {_{0}}
            & List.Accumulate(
              List.Positions(r), 
              {}, 
              (s, c) => {
                if C(r{c}) then
                  Number.Round(List.Average(List.FirstN(K(K(r, c + 1), C), each not C(_))))
                else
                  r{c}
              }
                & s
            )
        in
          s
    ), 
    Table.ColumnNames(Source)
  )
in
  S
Power Query solution 3 for Average Fill for Blank Months, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){0}[Content], 
  Calc = Table.AddColumn(
    Source, 
    "Custom", 
    each 
      let
        a = List.Skip(Record.ToList(_)), 
        b = List.Transform(List.PositionOf(a, null, Occurrence.All), each List.FirstN(a, _)), 
        c = List.Transform(
          {0 .. List.Count(b) - 1}, 
          each Number.Round(List.Average(List.Difference(b{_}, try b{_ - 1} otherwise {null})))
        ), 
        d = List.Transform(
          {0 .. List.Count(c) - 1}, 
          each try if c{_} = null then c{_ - 1} else c{_} otherwise null
        ), 
        e = List.Zip({d, List.PositionOf(a, null, Occurrence.All)}), 
        f = List.Sort(
          List.Select(List.Zip({a, List.Positions(a)}), each _{0} <> null) & e, 
          each _{1}
        ), 
        g = Table.FromRows({List.Transform(f, each _{0})}, List.Skip(Table.ColumnNames(Source)))
      in
        g
  )[[Company], [Custom]], 
  Sol = Table.ExpandTableColumn(Calc, "Custom", Table.ColumnNames(Calc[Custom]{0}))
in
  Sol
Power Query solution 4 for Average Fill for Blank Months, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){0}[Content], 
  Calc = Table.AddColumn(
    Source, 
    "A", 
    each Table.FromRows(
      {
        let
          a = List.Skip(Record.ToList(_)), 
          b = List.Transform(
            {0 .. List.Count(a) - 1}, 
            each 
              if a{_} is null then
                let
                  c = List.Reverse(List.Transform({0 .. _ - 1}, (x) => a{x})), 
                  d = List.PositionOf(c, null, Occurrence.All), 
                  e = 
                    if List.IsEmpty(d) then
                      Number.Round(List.Average(c))
                    else
                      Number.Round(List.Average(List.Transform({0 .. List.Last(d) - 1}, each c{_})))
                in
                  e
              else
                a{_}
          )
        in
          b
      }, 
      List.Skip(Table.ColumnNames(Source))
    )
  )[[Company], [A]], 
  Sol = Table.ExpandTableColumn(Calc, "A", Table.ColumnNames(Calc[A]{0}))
in
  Sol
Power Query solution 5 for Average Fill for Blank Months, proposed by Luan Rodrigues: 
let
 Fonte = Tabela1,
 sub = Table.ReplaceValue(Fonte,null,"|",Replacer.ReplaceValue,List.RemoveFirstN(Table.ColumnNames(Fonte))),
 tab = Table.AddColumn(sub, "Personalizar", each 
[a = Text.Split(Text.Combine(List.Transform(List.RemoveFirstN(Record.FieldValues(_),1),(x)=> Text.From(x)),", "),", |"),
b = List.Combine(List.Transform(a, each {_} & {Number.Round(List.Average(List.Transform(List.Select(Text.Split(Text.Remove(_,"|"),","), each _ <> "" ),Number.From )))})),
c = List.Select(List.Combine(List.Transform(b, each try Text.Split(Text.From(_),", ") otherwise {null})), each _ <> "|" and _ <> ""),
 d = Table.TransformColumnTypes(Table.FillDown(Table.FromColumns({List.RemoveLastN(c,1)}),{"Column1"}),{"Column1", type number}),
 e = Table.Transpose(if Table.RowCount(d) < List.Count(List.RemoveFirstN(Table.ColumnNames(Fonte))) then hashtag#table({"Column1"},{{null}})&d else d,List.RemoveFirstN(Table.ColumnNames(Fonte)) )
][e])[[Company],[Personalizar]],
 res = Table.ExpandTableColumn(tab, "Personalizar", List.RemoveFirstN(Table.ColumnNames(Fonte)))
in
 res
Power Query solution 6 for Average Fill for Blank Months, proposed by Eric Laforce: 
let
  Source = Excel.CurrentWorkbook(){[Name = "tData120"]}[Content], 
  Transform = List.Transform(
    Table.ToRows(Source), 
    each List.Accumulate(
      List.Skip(_), 
      [r = {_{0}}, nn = {}, prev = null], 
      (s, c) =>
        let
          _AvgNN = (c ?? Number.Round(List.Average(s[nn]))) ?? s[prev]
        in
          if (c is null) then
            [r = s[r] & {_AvgNN ?? s[prev]}, nn = {}, prev = _AvgNN]
          else
            [r = s[r] & {c}, nn = s[nn] & {c}, prev = s[prev]]
    )[r]
  ), 
  ToTable = Table.FromRows(Transform, Table.ColumnNames(Source))
in
  ToTable
Power Query solution 7 for Average Fill for Blank Months, proposed by Szabolcs Phraner: 
let
  //custom function to be used on rows 
  InsertAvg = (list) =>
    let
      List = list, 
      Pos = List.Positions(List), 
      Acc = List.Accumulate(
        Pos, 
        {}, 
        (s, c) =>
          let
            v = List{c}, 
            avg = try
              Number.Round(List.Average(List.Range(List, c - 2, c)))
            otherwise
              if Value.Type(List{c - 1}) = type text then null else List{c - 1}
          in
            if v = null then s & {avg} else s & {v}
      )
    in
      Acc, 
  Table = Excel.CurrentWorkbook(){[Name = "Table"]}[Content], 
  Transform = List.Transform(Table.ToRows(Table), InsertAvg), 
  TableFromRows = Table.FromRows(Transform, Table.ColumnNames(Table))
in
  TableFromRows

Solving the challenge of Average Fill for Blank Months with Excel

Excel solution 1 for Average Fill for Blank Months, proposed by Bo Rydobon 🇹🇭: 
=VSTACK(A1:G1,SCAN(0,A2:G6,LAMBDA(a,v,LET(b,TAKE(A2:v,-1),IF(v<>"",v,IFERROR(ROUND(AVERAGE(DROP(b,,XMATCH(0,-ISNUMBER(DROP(b,,-1)),,-1))),),IF(N(a),a,"")))))))
Excel solution 2 for Average Fill for Blank Months, proposed by محمد حلمي: 
=LET(b,A1:G6,r,LAMBDA(e,SCAN(0,b,LAMBDA(a,d,IF(N(d),a+d^e)))),SCAN(,IF(b>0,b,DROP(HSTACK(0,ROUND(r(1)/r(0),)),,-1)),LAMBDA(a,d,IFERROR(d,IF(N(a),a,"")))))
Excel solution 3 for Average Fill for Blank Months, proposed by Kris Jaganah: 
=HSTACK(A1:A6,REDUCE(B1:G1,BYROW(B2:G6,LAMBDA(z,LET(a,IF(ISBLANK(z),"+",z),b,SEQUENCE(,COLUMNS(a)),c,MAP(b,LAMBDA(x,SUM(--(TAKE(a,,x)=CHOOSECOLS(a,x))))),d,IFERROR(BYROW(IFERROR(DROP(--TEXTSPLIT(TEXTJOIN("-",,a),"-","+"),-1),""),LAMBDA(y,ROUND((AVERAGE(y)),0))),0),e,IFNA(XLOOKUP(a&c,"+"&SEQUENCE(ROWS(d)),d),a),ARRAYTOTEXT(IF(e=0,XLOOKUP(b-1,b,e,0),e))))),LAMBDA(p,q,VSTACK(p,--TEXTSPLIT(q,", ")))))
Excel solution 4 for Average Fill for Blank Months, proposed by Oscar Mendez Roca Farell: 
=VSTACK(A1:G1, SCAN( , A2:G6, LAMBDA(i, x, IF(x>0, x ,LET(_t, TAKE(A2:x, -1),_n, SUM(AGGREGATE(14, 6, COLUMN(_t)/(N(+_t)=0), {1,2})*{1,-1}), IFERROR(ROUND(AVERAGE(TAKE(_t, ,-_n)), ), N(i)))))))

&&&

Leave a Reply