Forum Discussion

LukeReds's avatar
LukeReds
Helper II
2 years ago
Solved

Sum n values in a table with List.Range

Hi to everyone,

i have a table like in the pic, i want to sum 4 value per rows, for example

row1:  135

rowe2: 135+165

row3: 135+165+195

row4: 135+165+195+225

row5:: 165+195+225+325

row6:  195+225+325+180

and  so on

 

I use this formula but it give me a wrong number of elements, someone could explain me why? And what have i to correct? In "list" (pic below) i aspected to have 4 elements...

 

if [week]< 4 then List.Range (Source[value], 0,[week]) else List.Range (Source[value], [week]-3,[week]-1)

 

Thank you in advance

 

 

Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    LukeReds 

    Try this way: 

    List.Range(Source[value],  [week]- 4, 4)


    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc25DQAxCETRXogdcPmqBbn/NpZZyB5fSBNBQoPEJr0RpPAqG3zLnlYtz7S1F34O/97oUv2gc/WLvqsL5+HcB5a9lwXTjun3AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [week = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"week", Int64.Type}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Range(Source[value],  [week]- 4, 4) )
in
    #"Added Custom"

      


    List.Range works with an offset and a count:

    List.Range(list as list, offset as number, optional count as nullable number) as list



2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    LukeReds 

    Try this way: 

    List.Range(Source[value],  [week]- 4, 4)


    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc25DQAxCETRXogdcPmqBbn/NpZZyB5fSBNBQoPEJr0RpPAqG3zLnlYtz7S1F34O/97oUv2gc/WLvqsL5+HcB5a9lwXTjun3AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [week = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"week", Int64.Type}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Range(Source[value],  [week]- 4, 4) )
in
    #"Added Custom"

      


    List.Range works with an offset and a count:

    List.Range(list as list, offset as number, optional count as nullable number) as list