Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

insert no of record based on the value from another table

from the sprint coloum , if i have value as 2 - need to show iteration 1 and 2 has 100% capacity 

1 Reply

  • Since the question is a bit terse, here's an approach based on what your table shows: your "Sprint" column (Story point / Velocity) represents how many iterations of capacity a team's story points will consume, e.g. Sprint = 2.1 means "2 full iterations at 100%, plus a partial 3rd iteration at 10%". If that's the goal, here's a Power Query (M) approach that expands each team's row into one row per iteration, capped at 100% per row:

     

    1. Add a custom column that generates a list of iteration numbers from 1 up to the rounded-up Sprint value:

    Iterations = List.Numbers(1, Number.RoundUp([Sprint], 0))

     

    2. Expand that list into new rows (right-click the column > Expand to New Rows).

     

    3. Add another custom column for the capacity used in that specific iteration, capped at 100%:

    Capacity = if [Sprint] - ([Iterations] - 1) >= 1 then 1 else [Sprint] - ([Iterations] - 1)

     

    4. Format that Capacity column as a percentage.

     

    For your example (Sprint = 2.1): this produces Iteration 1 = 100%, Iteration 2 = 100%, Iteration 3 = 10% - i.e., "iteration 1 and 2 have 100% capacity" plus a correctly-sized remainder row, all driven dynamically off the Sprint value rather than hardcoding "2".

     

    If this isn't quite what you meant by "insert no of record based on the value", let us know a bit more about the desired output (e.g. do the extra rows need a corresponding "Next Iteration" label, and should rows with Sprint <= 1 stay as a single row?) and it can be refined further.