Forum Discussion

BlueSky's avatar
BlueSky
Icon for Helper I rankHelper 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
Icon for Super User rankSuper User
6 years ago

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

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper 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
    Icon for Community Champion rankCommunity 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