Power Query Accumulation where the first value is text

Hi Dicken 

The core reason for the failure with the {} seed (in that the resulting list contains nulls rather than cumulative sums of numbers) is that arithmetic operators produce a null result when one of the operands is null. In this case, null + c = null.

See here for the documentation on the addition operator:

https://learn.microsoft.com/en-us/powerquery-m/m-spec-operators#addition-operator

More specifically, the {} seed version fails when alist starts with a text value because:

1. On the 1st iteration of List.Accumulate, a text value is added to the accumulated list.
2. In the 2nd iteration onwards, when the alist item is a number:
   1. The List.Last expression returns null because there are no non-null numbers in the accumulated list s, so x = null.
   2. So x + c = null, because x = null.
   3. So null is added to the accumulated list.

Possible solutions:

1. When the List.Last expression returns null, convert it to zero using the null-coalescing operator ??:

let
  alist = {"b", 2, 2, 2, "a", "b", 2, 2, 2, "c", 2, 2, 2}
in
  List.Accumulate(
    alist,
    {},
    (s, c) =>
      let
        x =
          List.Last(List.Select(s, (x) => Value.Is(x, type number))) ?? 0
      in
        s & {try x + c otherwise c}
  )

2. Use List.Sum (which ignores nulls, unlike the + operator) to sum x & c:

let
  alist = {"b", 2, 2, 2, "a", "b", 2, 2, 2, "c", 2, 2, 2}
in
  List.Accumulate(
    alist,
    {},
    (s, c) =>
      let
        x =
          List.Last(List.Select(s, (x) => Value.Is(x, type number)))
      in
        s & {try List.Sum({x,c}) otherwise c}
  )

Do either of these work for you?