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 guessi...
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)
parry2k's avatar
parry2k
Super User
6 years ago

BlueSky it will be super easy to do in power query, is that ok?

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