Custom Filter function

%3CLINGO-SUB%20id%3D%22lingo-sub-2579722%22%20slang%3D%22en-US%22%3ECustom%20Filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2579722%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20a%20custom%20filter%20function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22192%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3EA1%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EA2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EA3%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EA1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EA4%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EA1%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EA2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3EA2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20sth%20similar%20to%20above%20table%20already%20in%20Power%20BI.%20What%20I%20am%20now%20looking%20for%20is%20a%20filter%20that%20searches%20the%20first%20column%20(combination%20of%20text%20and%20numbers)%20and%20removes%20all%20lines%20which%20are%20listed%20less%20than%202%20times.%20SO%20in%20the%20above%20example%20I%20would%20need%20to%20get%20rid%20of%20the%20following%20lines%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22192%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2215px%22%3EA1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2215px%22%3EA2%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2215px%22%3E%3CSTRIKE%3EA3%3C%2FSTRIKE%3E%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E%3CSTRIKE%3E1%3C%2FSTRIKE%3E%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E%3CSTRIKE%3E2%3C%2FSTRIKE%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2215px%22%3EA1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2215px%22%3E%3CSTRIKE%3EA4%3C%2FSTRIKE%3E%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E%3CSTRIKE%3E1%3C%2FSTRIKE%3E%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E%3CSTRIKE%3E2%3C%2FSTRIKE%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2215px%22%3EA1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2215px%22%3EA2%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%20height%3D%2215px%22%3EA2%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%20height%3D%2215px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20might%20be%20very%20easy%20but%20I%20can't%20think%20of%20a%20smart%20solution%20at%20the%20moment.%20Does%20anyone%20have%20an%20idea%20how%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20and%20cheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2579722%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2579854%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2579854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413468%22%20target%3D%22_blank%22%3E%40RaMa87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIf%20I%20understand%20the%20translation%20correctly%2C%20look%20for%20a%20way%20to%20keep%20duplicates%20and%20delete%20individual%20entries.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIf%20so%2C%20here%20is%20an%20example%20with%20VBA%20in%20the%20inserted%20file.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2579902%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2579902%22%20slang%3D%22en-US%22%3EHi%20Nikolino%2C%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%20for%20the%20answer%20-%20in%20retrospective%20my%20wording%20was%20maybe%20not%20correct.%20I%20want%20to%20keep%20all%20lines%20which%20are%20listed%20more%20than%202%20times%2C%20i.e.%20delete%20all%20entries%20with%20only%20one%20or%20two%20lines.%20So%20keep%20everything%20%3D%26gt%3B3%20and%20delete%20the%20others%20--%26gt%3B%20so%20the%20problem%20is%20that%20I%20do%20not%20simply%20need%20to%20delete%20individual%20entries.%3CBR%20%2F%3EThe%20filter%20would%20also%20need%20to%20be%20within%20Power%20BI%20(i.e.%20the%20query%20I%20set%20up)%20otherwise%20it%20will%20make%20the%20whole%20automated%20output%20useless.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi guys,

 

I am looking for a custom filter function

 

A112
A212
A312
A112
A412
A112
A212
A212

 

I have sth similar to above table already in Power BI. What I am now looking for is a filter that searches the first column (combination of text and numbers) and removes all lines which are listed less than 2 times. SO in the above example I would need to get rid of the following lines:

 

A112
A212
A312
A112
A412
A112
A212
A212

 

It might be very easy but I can't think of a smart solution at the moment. Does anyone have an idea how to do this?

 

Thanks and cheers

 

5 Replies

@RaMa87 

If I understand the translation correctly, look for a way to keep duplicates and delete individual entries.

If so, here is an example with VBA in the inserted file.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Hi Nikolino,

thanks for the answer - in retrospective my wording was maybe not correct. I want to keep all lines which are listed more than 2 times, i.e. delete all entries with only one or two lines. So keep everything =>3 and delete the others --> so the problem is that I do not simply need to delete individual entries.
The filter would also need to be within Power BI (i.e. the query I set up) otherwise it will make the whole automated output useless.
Regarding Power BI, unfortunately I can't help you. My knowledge in this area is very limited.
Maybe, one of the specialists here who are very familiar with Power BI could help you.

I wish you continued success with Excel

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)
Problem solved
I changed strategy and simply removed individual lines which was easy with the "Keep Rows" --> "Keep Duplicates" function
I am pleased that you have come to a solution yourself.
Please visit the forum again soon, there is always someone happy to respond to your reply.

I wish you continued success with Excel.

NikolinoDE