cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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)

2 ACCEPTED SOLUTIONS
Super User

@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

List.Range works with an offset and a count:

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

thank you Fowmy, shkran lak

2 REPLIES 2
Helper II

thank you Fowmy, shkran lak

Super User

@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

List.Range works with an offset and a count:

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

Did I answer your question? Mark my post as a solution! and hit thumbs up

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.