Filter with new column contains any combination of certain values

[Anonymous]

Hello and Thanks in advance. 

I need to add a filter to a report to only show parts that have any combination of certain codes in the code column. Sample of what I am looking for is shown in a list below.  

The report filter should only show parts with code "MPK" and then any combination of these other codes: "HFR, RTG, MSD, MSW, MWT, P48"

For example, these should all be included: "MPK" or "MPK,MSW,MWT,RTG" or "MPK,RTG" or "MPK,MSW,P48" but this one should not be included: "HLI,MPK,MSW,MT2,MTC,MTT,RTG"

I thought about adding a column with an IF statement that gives a "Y" or "N" and then I could filter the page to include only the "Y's. 

Can someone help with what I would put as the formula for that new column?

Code	Include?
MPK	Y
MPK,MSW	Y
MPK,MSW,MT2	N
MPK,MSW,MT2,MT3	N
MPK,MSW,MT2,MTC,MTT,RTG	N
MPK,MSW,MT2,MT3,RTG	N
MPK,MSW,MT2,MWT,RTG	N
MPK,MSW,MT2,RTG	N
MPK,MSW,MWT	Y
MPK,MSW,MWT,RTG	Y
MPK,MSW,RTG	Y
MPK,MT2	N
MPK,MT2,MT3	N
MPK,MT2,MT3,RTG	N
MPK,MT2,MWT	N
MPK,MT2,MWT,RTG	N
MPK,MT2,RTG	N
MPK,MWT	Y
MPK,P48	Y
MPK,RTG	Y

[camargos88 · Accepted solution]

Hi @Anonymous ,

You can do it using Power Query.

Create a table with the values you want to keep and create a new column to filter them, like:

let _lst = Combination[Item] in

if List.Count(List.Difference(Text.Split([Code], ","), _lst)) = 0 then "Y" else "N"

Be aware that Power Query is case sensitive, so clean the data before compare the values.

See the attached file for more details.

[Anonymous]

Thanks so much @camargos88 ! This worked great for me.

The only issue I had was that some of the parts had no codes so was giving me an error. To fix this I just transformed the errors in to "N" and I was able to use the filter. Thanks again!