Forum Discussion

lauravd's avatar
lauravd
Frequent Visitor
8 years ago
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 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.

 

 

Need Help
Show and Tell
  • stretcharm's avatar
    stretcharm
    8 years ago

    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"

5 Replies

  • stretcharm's avatar
    stretcharm
    Memorable Member
    8 years ago

    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's avatar
      lauravd
      Frequent Visitor
      8 years ago
      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's avatar
        stretcharm
        Memorable Member
        8 years ago

        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"