Forum Discussion

BlueSky's avatar
BlueSky
Helper I
6 years ago
Solved

Generate a table with incremental values

So I'd like the table below:

 

Day

Value

1

0

2

0.5

3

1

4

1.5

5

2

 

I can generate two different tables but can't bring them together, I'm guessing this is pretty simple can someone please help? 

 

Generates a table with Value 

GENERATESERIES ( 0, 2, 0.5 ) 

 

Generates a table with Day

GENERATESERIES ( 1, 5, 1 ) 

 

Thanks

Mark

Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    BlueSky in power query , do the following:

     

    - add a blank query

    - click advanced editor

     

    and paste following code

     

    let

    #"Source" = Table.FromList({1..5}, Splitter.SplitByNothing(),{"index"},null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Source", "Factor", 0,0.5),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"index", Int64.Type}})
in
    #"Changed Type"

     

    I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

  • DataZoe's avatar
    DataZoe
    6 years ago

    BlueSky You could do it this way, if you didn't want to do it in M:

     

    Table = ADDCOLUMNS(GENERATESERIES ( 0, 20, 0.5 ) ,"Day", divide([value],0.5)+1)

4 Replies

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      BlueSky in power query , do the following:

       

      - add a blank query

      - click advanced editor

       

      and paste following code

       

      let

    #"Source" = Table.FromList({1..5}, Splitter.SplitByNothing(),{"index"},null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Source", "Factor", 0,0.5),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"index", Int64.Type}})
in
    #"Changed Type"

       

      I would 💖 Kudos 🙂 if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    • camargos88's avatar
      camargos88
      Community Champion
      6 years ago

      BlueSky ,

       

      In addition to parry2k  answer, this is the dax version:

       

      Table 2 =

      VAR _day = GENERATESERIES(1;5;1)
      VAR _value = GENERATESERIES(0;2;0,5)

      VAR _day2 = SELECTCOLUMNS(ADDCOLUMNS(_day; "Index"; RANKX(_day; [Value];;ASC)); "Day"; [Value]; "Index"; [Index])
      VAR _value2 = ADDCOLUMNS(_value; "Index2"; RANKX(_value; [Value]; ;ASC))

      RETURN
      SELECTCOLUMNS(ADDCOLUMNS(_day2; "Value"; SELECTCOLUMNS(FILTER(_value2; [Index] = [Index2]); "Value"; [Value])); "Day"; [Day]; "Value"; [Value])
       
      PQ is much easier than DAX for it. 😃
       
      Ricardo
  • DataZoe's avatar
    DataZoe
    Microsoft Employee
    6 years ago

    BlueSky You could do it this way, if you didn't want to do it in M:

     

    Table = ADDCOLUMNS(GENERATESERIES ( 0, 20, 0.5 ) ,"Day", divide([value],0.5)+1)