Expanding List.Numbers - wrong results

[lauravd]

Hi, 

I am trying to get a list of dates reoccuring every 21 day from start date. Duration of first event is 442days/21days between events =21 times event should happen. By expanding my list I should get 21 row with same event_ref number, but it changes all event_ref numbers. 

I've got a table as per picture below:

My Custom Column is created using this fuction:

Once I expand my list as new rows it changes event references.

Any Ideas? 

Thank you.

[stretcharm]

Your syntax is wrong

List.Numbers(start as number, count as number, optional increment as nullable number) as { Number }

The 2nd param is count (not end) so if you want 21 rows it should be  21 or the Duration/21

[lauravd]

Hi, 21 is just an example number as other events (check event ref) have different duration so occurrence as will be different ( not 21 rows for each event). Any more ideas?:)

Thanks.

[stretcharm · Accepted solution]

Is this what your expecting?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NgFiJR0lEyMLA0sQbWxkagikjQyVYnWACszMzYEiYAkDmAJzUxQFZpZQCQNTCG1oCTMhFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, start_date = _t, end_date = _t, Intervals = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"start_date", Int64.Type}, {"end_date", Int64.Type}, {"Intervals", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type", "Duration", each ([end_date]-[start_date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom3", "Custom.1", each List.Numbers([start_date],[Duration]/21,21)),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1")
in
    #"Expanded Custom.1"

[lauravd]

Thank you once again. Got it all sorted.

[lauravd]

Hi, 

Yes, this is what I am expecting. Thank you.