Power Query Accumulation where the first value is text

[Dicken]

Hi, 
   As a starting point ; 
                      let alist = {3,2,2,2,"a","b",2,2,2,"c",2,2,2}
                              in List.Accumulate( alist, {} , (s,c)=>
                                               let x = if List.Count(s) = 0 then 0 else List.Last(List.Select(s, (x)=> Value.Is(x, type number)))
                                                              in s & { try x + c otherwise c} )

which works, but if the first value is text  then this does not work, you can change the seed to  {0} , and then skip ; so ; 

let alist = {"a",2,2,2,"a","b",2,2,2,"c",2,2,2}
                 in   
                     List.Accumulate( alist, {0} , (s,c)=>   

                                        s & { try List.Last( List.Select( s, (x)=>Value.Is(x, type number))) + c otherwise c } )

So A; why I can't see why the  {} seed fails   can anyone help, I'm more interested in why it fails that just a solution with no 
information of links to where I can find it. 

Richard. 

[V-yubandi-msft]

Hi @Dicken,

Thanks for connecting with us on the Microsoft Fabric Community Forum.

• The following code initializes the accumulator with a list containing a single element, 0. This provides a starting point for subsequent numeric operations.
• You can use any initial value for the accumulator to start the accumulation process, and it will work perfectly.

Code:

= List.Accumulate(

        alist,

        {0}, // Start with a numeric value

        (s, c) =>

            s & {

                try List.Last(List. Select(s, (x) => Value. Is(x, type number))) + c

                otherwise c

            }

    )

Output:

If my answer addressed your query, kindly mark it as the Accepted Solution to assist others.

I'd also be grateful for a 'Kudos' if you found my response useful! 

[wdx223_Daniel]

[OwenAuger · Accepted solution]

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?

[Dicken]

Just as a for the reord I've also found List.Generate works well, probably better; 

let alist = {"a",3,2,"a","b",2,2,2,"c",2,2}
in List.Generate( ()=> [ x = 0 , y = 0 ],
each [x] < List.Count(alist) ,
each [x = [x]+1, y = if Value.Is( alist{x} ,type number) then [y] + alist {x} else [y] ],
each if Value.Is( alist{[x]} , type number) then [y] else alist{[x]} )

[Dicken]

Yes, I had used sum, which also means if you're not dealing with text you can still start with {} 
and not have the leadign zero .  I like the use of coalece which is something I forget about, 
it also the advantage of being understandable, 

[Dicken]

Thanks, so it's the null that causes the problem.