Forum Discussion
Sum OF values
Hi Everyone ,
need your help
i want use power query and generate the result below
I have with two columns first is original col and second one is derived column as mentioned the below
i want to amke a running toatl but in different way
in the example listed below if the values are not macthing from both columns a and b then the deafualt value is will 1 in the result col
if the values are macthing then it should be add 1 to each values
| A | B | result | ||
| 1 | NULL | initial value is nulll then it should be 1(in short values are not matching) | 1 | |
| 1 | 1 | it is matching so added above value 1+ 1 | 2 | |
| 2 | 1 | both values are not macthing so | 1 | |
| 2 | 2 | macthing then adding above 1+1 | 2 | |
| 2 | 2 | macthing so above 2+1 | 3 | |
| 3 | 2 | not macthing so 1 | 1 | |
| 4 | 3 | not macthing so 1 | 1 | |
| 4 | 4 | macthing so above 1 value and add 1 into that so 1+1 | 2 |
fab196 , a new column
new column =
vat _b = [B]+0
return
countx(filter(Table, [A] = earlier([A]) && [B]+0 <= +b ), [A])
Hi fab196 ,
According to your description, it's more easier to achieve it by DAX, here's my solution.
1.In Power Query, add an index column.
Get this table.
2.Create a calculated column.
Count = VAR _RANK = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[ColumnA] = EARLIER ( 'Table'[ColumnA] ) && 'Table'[ColumnB] = EARLIER ( 'Table'[ColumnB] ) && 'Table'[ColumnA] = 'Table'[ColumnB] ), 'Table'[Index], , ASC, DENSE ) RETURN IF ( 'Table'[ColumnA] = 'Table'[ColumnB], _RANK + 1, _RANK )Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
fab196 , a new column
new column =
vat _b = [B]+0
return
countx(filter(Table, [A] = earlier([A]) && [B]+0 <= +b ), [A])
can we do it using power query?fab196 , refer if this can help
Hi fab196 ,
According to your description, it's more easier to achieve it by DAX, here's my solution.
1.In Power Query, add an index column.
Get this table.
2.Create a calculated column.
Count = VAR _RANK = RANKX ( FILTER ( ALL ( 'Table' ), 'Table'[ColumnA] = EARLIER ( 'Table'[ColumnA] ) && 'Table'[ColumnB] = EARLIER ( 'Table'[ColumnB] ) && 'Table'[ColumnA] = 'Table'[ColumnB] ), 'Table'[Index], , ASC, DENSE ) RETURN IF ( 'Table'[ColumnA] = 'Table'[ColumnB], _RANK + 1, _RANK )Get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.