Merging Rows and add a Custom Collumn- Power Query

[muggydaniel]

Hi all 

I have a scenario wherein I cannot find the answer, hoping to get some help with the below issue: 

Set of data is a row for every event that happens 

Application	Type	Reworked	In Queue	Amount
Gary	Credit Card	No	In Progress	$10,000
Gary	Credit Card	Yes	Completed	$10,000
Anne	Loan	No	In Progress	$20,000

I am only looking to report on data that is In Queu - In Progress however each instance needs a custom collumn to take into account the amount of instances that it has been reworked 

Looking to be able to achieve the below > without affecting lines that haven't been reworked

Application	Type	Reworked	In Queue	Amount	Amount of Reworks
Gary	Credit Card	No	In Progress	$10,000	1
Anne	Loan	No	In Progress	$20,000	0

How can this be achieved?

Kind regards, 

[Anonymous · Accepted solution]

Hi  @muggydaniel ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table1=
FILTER(
    'Table','Table'[In Queue] = "In Progress")
return
ADDCOLUMNS(
   _table1,"1",
    COUNTX(
        FILTER('Table',
        'Table'[Application]=EARLIER('Table'[Application])&&NOT('Table'[In Queue]) = "In Progress"),[In Queue])
   )

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly