Forum Discussion
Calculate Subtotals based on column value in Power Query M
I have been trying to find a solution for this, but I just can't get my head around it. I want to do a kind of SUMIFS in Power Query Editor using M-Language. So I have a table like this: ...
Thanks AnkitBI for your solutions, but not exactly what I was looking for.
I simplified my problem too much :) My indexes are way more complicated than that.
So I also have indexes like:
| Index | Row type | Value | Sum |
| 1 | Header | 300 | |
| 1.1 | Value | 100 | 100 |
| 1.2 | Value | 200 | 200 |
| 2 | Header | 1100 | |
| 2.1 | Value | 200 | 200 |
| 2.2 | Value | 400 | 400 |
| 2.3 | Header | 500 | |
| 2.3.1 | Value | 150 | 150 |
| 2.3.2 | Header | 350 | |
| 2.3.2.1 | Value | 350 | 350 |
So I don't have any set maximum of how long the indexes can be... At the moment my biggest index is:
1.1.1.1.1.1 But I don't want to make a code that is limited to certain index size, it should be able to handle any size index. That's why using something like "StartsWith" would be ideal because it would just simply summarize all the values on rows who's index starts with the index on current row. (it can summarize header rows too, because they are blank / zero)
