Syndicate_Admin
3 years agoAdministrator
Add rows to table that repeats unique column countdown starting from value in another column, for a set number of times.
Having trouble figuring out the direction I need to go for this table I want to make. Will continue to research functions but wanted to pose the general question to the community.
I have this table built in power query->
| Toolname | Toollife | Currentlife | Cost |
| Alpha | 5 | 5 | 50 |
| Bravo | 5 | 2 | 20 |
| Charlie | 3 | 3 | 30 |
| Delta | 6 | 6 | 30 |
and need an output table like this->
| part count | Alpha | Bravo | Charlie | Delta | total changes | total cost |
| 1 | 1 | 0 | 1 | 1 | 3 | 110 |
| 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 0 | 1 | 0 | 0 | 1 | 20 |
| 4 | 0 | 0 | 1 | 0 | 1 | 30 |
| 5 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | 1 | 0 | 0 | 0 | 1 | 50 |
| 7 | 0 | 0 | 1 | 1 | 2 | 60 |
| 8 | 0 | 1 | 0 | 0 | 1 | 20 |
| 9 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10 | 0 | 0 | 1 | 0 | 1 | 30 |
Where the 1 values under the name columns are the cells in this table where cellvalue=Toollife of [columnname]. The countdown under each [Toolname] column should start from the [Currentlife] value for the same Toolname set in the original table. With [part count] counting up from 1 to #"Default_Values"{0}[part_count] (this is another table with some default values built into it).
| part count | Alpha | Bravo | Charlie | Delta |
| 1 | 5 | 2 | 3 | 6 |
| 2 | 4 | 1 | 2 | 5 |
| 3 | 3 | 5 | 1 | 4 |
| 4 | 2 | 4 | 3 | 3 |
| 5 | 1 | 3 | 2 | 2 |
| 6 | 5 | 2 | 1 | 1 |
| 7 | 4 | 1 | 3 | 6 |
| 8 | 3 | 5 | 2 | 5 |
| 9 | 2 | 4 | 1 | 4 |
| 10 | 1 | 3 | 3 | 3 |
TIA.