Forum Discussion

mt_729's avatar
mt_729
Regular Visitor
2 years ago
Solved

Create a table based on two values with increments in Power Query

Hello,   I'm trying to create a dynamic table which will receive an input for minimum and maximum targets. The query should then add rows with 0.1 increments. What I find difficult is that a colum...
Need Help
Show and Tell
Tips and Tricks
  • AlienSx's avatar
    AlienSx
    2 years ago

    hello, mt_729 

    let
    Source = your_table,
    gen = List.Generate(
        () => [Target = Source{[Type = "Minimum"]}[Target], Payout = Source{[Type = "Minimum"]}[Payout]],
        (x) => x[Target] <= Source{[Type = "Maximum"]}[Target],
        (x) => [Target = x[Target] + .001, Payout = List.Min({Source{[Type = "Maximum"]}[Payout], x[Payout] + .015})]
    ),
    z = Table.FromRecords(gen)
in
    z
rubayatyasmin's avatar
rubayatyasmin
Community Champion
2 years ago

Hi, mt_729 

 

First, you need to create a basic table with your minimum and maximum target values. You can enter these directly in Power BI using "Enter Data" from the Home ribbon or import them from an external source. (I am guessing you have one already)

 

* create a calculated table

 

TargetTable =
VAR MinTarget = 95.0
VAR MaxTarget = 98.0
VAR TargetIncrement = 0.1
VAR MinPayout = 75
VAR PayoutIncrement = 1.5
VAR MaxPayout = 120
RETURN
GENERATESERIES(MinTarget, MaxTarget, TargetIncrement)

 

this dax will create a single column table with values from 95.0 to 98.0 incremented by 0.1.

 

Now add a calculated column in your newly created TargetTable

 

Payout =
VAR BasePayout = [Target] - 95.0
RETURN
IF(
[Target] <= 98.0,
MIN(MinPayout + (BasePayout * (PayoutIncrement / TargetIncrement)), MaxPayout),
MaxPayout
)

 

Replace [Target] with the actual column name that contains the target values.