Forum Discussion
Solved
Expanding List.Numbers - wrong results
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 sho...
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"
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
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.
Thanks.
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"