Forum Discussion
Helper IIAssign specific value to repeating blanks in a column based on the order they're in
Hello - I have a table similiar to the sample table below. The subtotal1/subtotal2/total does not pull in with my query due to the way the files are structured and pulled in. The blanks will always be in the same order for each project but not necessarily in the same amount of rows from the previous section. (ie subtotal 1 could have 15 rows above it in Project A and 25 above it in Project B). I need to create a new column that assigns subtotal1/subtotal2/total based on these blanks.
I have tried this custom column but it does not work, it just spins & spins and never even shows an error.
= Table.AddColumn(#"Added Index", "Custom", each let
__Project = [Project Number],
__Index = [Index],
__Table = Table.SelectRows( #"Added Custom", each ([Project Number] = __Project and [Index] > __Index ) )
in
Table.RowCount(__Table) + 1)
| Project | kWh Category | Item Category | Item Type | $ | New KWH Category |
| A | Engineering | BESS Engineering | Electrical | 100 | Engineering |
| A | MBOM Material | BESS Material | BESS Material | 200 | MBOM Material |
| A | BOP Material | Electrical BOP Material | Aux Material | 300 | BOP Material |
| A | EPC BOP | BOP Civil | Civil | 400 | EPC BOP |
| A | Substation | Substation | Substation Engineering | 500 | Substation |
| A | Transmission Line | Transmission Line | Transmission Line | 600 | Transmission Line |
| A | Substation | Substation | Substation | 700 | Substation |
| A | Freight | Freight | MBOM+BOP Freight | 800 | Freight |
| A | EPC BOP | Other | Insurance | 900 | EPC BOP |
| A | MBOM Material | BESS Material | Warranties | 1000 | MBOM Material |
| A | Project Services | Site Labour | Site Labour | 1100 | Project Services |
| A | 6600 | Subtotal 1 | |||
| A | EPC BOP | Other | Permitting | 1300 | EPC BOP |
| A | Contingency | Sourcing Contingency | Sourcing Contingency | 1400 | Contingency |
| A | 9300 | Subtotal 2 | |||
| A | Sales Tax | Sales Tax | Sales Tax | 1600 | Sales Tax |
| A | Bonding | Bonding | Bonding | 1700 | Bonding |
| A | 12600 | Total | |||
| B | Engineering | BESS Engineering | Electrical | 100 | Engineering |
| B | MBOM Material | BESS Material | BESS Material | 200 | MBOM Material |
| B | BOP Material | Electrical BOP Material | Aux Material | 300 | BOP Material |
| B | EPC BOP | BOP Electrical | BESS Site Commissioning | 400 | EPC BOP |
| B | Substation | Substation | Substation Engineering | 500 | Substation |
| B | Transmission Line | Transmission Line | Transmission Line | 600 | Transmission Line |
| B | Substation | Substation | Substation Commissioning | 700 | Substation |
| B | Freight | Freight | MBOM+BOP Freight | 800 | Freight |
| B | EPC BOP | Other | Insurance | 900 | EPC BOP |
| B | MBOM Material | BESS Material | Warranties | 1000 | MBOM Material |
| B | Project Services | Site Labour | Site Labour | 1100 | Project Services |
| B | 6600 | Subtotal 1 | |||
| B | EPC BOP | Other | Permitting | 1200 | EPC BOP |
| B | Contingency | Sourcing Contingency | Sourcing Contingency | 1300 | Contingency |
| B | Contingency | Risk Contingency | Risk Contingency | 1400 | Contingency |
| B | 10500 | Subtotal 2 | |||
| B | Sales Tax | Sales Tax | Sales Tax | 1500 | Sales Tax |
| B | Bonding | Bonding | Bonding | 1600 | Bonding |
| B | 13600 | Total |
4 Replies
- Ashish_Mathur
Super User
Hi,
Not sure of what you want but you should never create subtoal/total rows in any table. These summarisation rows get created automatically when you create Table/matrix visuals.
jmdaily83Ashish_Mathur Thank you for your response. That's how I would typically do it, but in this particular visual the subtotals need to add together and this does not happen naturally in a matrix. So I need Subtotal 1, Subtotal 2 = subtotal 1 + subtotal 2, total = item A + item B + Subtotal 1 + Subtotal 2
Current matrix functionality
What I need - (notice no subtotal 3) and subtotals are added together
Hi jmdaily83 ,
You'd better add a level2 column in you table to dispaly your second level. And it's best to determine this level and import it together before importing, which is more convenient.
Column = CALCULATE(SUM('Table'[$]),FILTER('Table',[Project]=EARLIER('Table'[Project])&&[Level2]<=EARLIER('Table'[Level2])))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- jmdaily83
The end user ended up adjusting the way things were labeled on their end so I no longer needed to do anything custom on my end.
thank you all for your help
