Filter and remove data in Power Query

[Anonymous]

I am trying to automate a series of manual tasks in Excel to determine the accurate amount of documents completed in a day. Each line represents a single product and document. 

First, the user filters column G (original quantity required) for negative values and highlights these rows (to be deleted later), as quantity required should be positive and the receiver numbers corresponding with negative original quantities do not represent real data.

The user removes filter on column G and applies second filter on column F (Receiver number) for the same receiver number values highlighted (checks values individually). As mentioned above, the receiver numbers corresponding with these negative values are not real and should not be incorporated into the "traveler counter." This step is completed because some of these receiver numbers can also result in lines with a positive original quantity required.

User then deletes all the resulting rows both highlighted and not and resums the traveler counter in column H. 

^all rows to be completed based on false receiver numbers. 

I am looking for a way to automate these steps either in Power Query or Power BI. I have tried removing duplicates of receiver number and filtering for values greater than zero in the "original quantity required column" but it still leaves some false positives behind as some of these fake receiver numbers can correspond with positive values. 

Any insight would be appreciated. 

[serpiva64]

Hi,

can you post some fake data (not images) and the result you want to obtain

[Anonymous]

Rec #      original qty      traveler counter

226171		1		1
226331		1		1
226243		1		1
226246		1		1
226196		1		1
226316		2		1
226305		1		1
226296		1		1
226287		1		1
226221		1-		1
226221		1		1
226314		1		1
226230		1		1
226251		1		1
226259		1		1
226308		1		1
226286		1		1
226370		1		1
226307		2		1
226137		1		1
226273		2		1
226328		1		1
226217		1		1
226146		1		1
226265		1		1
226358		1		1
226190		2		1
226279		1		1
226148		1		1
226323		1		1
226317		1		1
226367		1		1
226166		1		1
226453		1		1
226165		1-		1
226165		2		1

current counter: 36

After filtering column 2 for negative values, gathering the corresponding receiver #s, refiltering column 1 for those values, and deleting all resulting rows, the counter should be 32

[serpiva64 · Accepted solution]

Hi,

You can try this way:

- add a custom column

- group by

- choose columns

- expand

- and finally filter rows (and delete unnecessary column)

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !