Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Filter with new column contains any combination of certain values

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?

 

CodeInclude?
MPKY
MPK,MSWY
MPK,MSW,MT2N
MPK,MSW,MT2,MT3N
MPK,MSW,MT2,MTC,MTT,RTGN
MPK,MSW,MT2,MT3,RTGN
MPK,MSW,MT2,MWT,RTGN
MPK,MSW,MT2,RTGN
MPK,MSW,MWTY
MPK,MSW,MWT,RTGY
MPK,MSW,RTGY
MPK,MT2N
MPK,MT2,MT3N
MPK,MT2,MT3,RTGN
MPK,MT2,MWTN
MPK,MT2,MWT,RTGN
MPK,MT2,RTGN
MPK,MWTY
MPK,P48Y
MPK,RTGY
1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

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"

 

Capture.PNG

 

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

 

See the attached file for more details.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

2 REPLIES 2
camargos88
Community Champion
Community Champion

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"

 

Capture.PNG

 

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

 

See the attached file for more details.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors