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
patlopes
Frequent Visitor

Filter one colunm by another two columns

HI,

I have a table as sample below

patlopes_0-1680860918470.png

I need to filter the table by Category when all Sub-Categories are Inactive. In the sample, after filtering, the table would only show Cat B. 

Is it possible in Power Query ?

 

Thank you

1 ACCEPTED SOLUTION
SamInogic
Super User
Super User

Hi @patlopes ,

 

As per our Understandings you are looking to see the CatB Category in the table, it should Filter with the Sub-Category and the Status,


You can achieve this by GROUP BY initially Use group by as below Screenshot, 
filteronecolumn1.png

 

There will be an Error, 

filteronecolumn2.png


We have to make Changes in the Highlighted Code,

= Table.Group(#"Changed Type", {"Category"}, {{"Status", each Text.Combine([Status],","), type nullable text}})

 

Finally, we can use the Text.Contains Methods in Status to find any of them is Active, 

 

Add a custom column with Below Power Query Expression 

Text.Contains([Status],"Active")

filteronecolumn5.png

filteronecolumn6.png

Thereafter you can Select False in the Filter 

filteronecolumn7.png

filteronecolumn8.png

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

View solution in original post

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

Use this

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUXBU0lFyNAQRySWZZalKsTpI4kY4xI2BhGdeIqqME1DQyRCnjBE2GWegoDNWPWAZLPaDxY2RxGMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub-category" = _t, Status = _t]),
    l = List.Transform(List.Select(Table.ToRecords(Source), (x)=>x[Status]="Active"),(x)=>x[Category])
in
    Table.FromRecords(List.Select(Table.ToRecords(Source),(x)=> not List.Contains(l,x[Category])),Table.ColumnNames(Source))

 

 

SamInogic
Super User
Super User

Hi @patlopes ,

 

As per our Understandings you are looking to see the CatB Category in the table, it should Filter with the Sub-Category and the Status,


You can achieve this by GROUP BY initially Use group by as below Screenshot, 
filteronecolumn1.png

 

There will be an Error, 

filteronecolumn2.png


We have to make Changes in the Highlighted Code,

= Table.Group(#"Changed Type", {"Category"}, {{"Status", each Text.Combine([Status],","), type nullable text}})

 

Finally, we can use the Text.Contains Methods in Status to find any of them is Active, 

 

Add a custom column with Below Power Query Expression 

Text.Contains([Status],"Active")

filteronecolumn5.png

filteronecolumn6.png

Thereafter you can Select False in the Filter 

filteronecolumn7.png

filteronecolumn8.png

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.


Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

Inogic Professional Services: Power Platform/Dynamics 365 CRM
An expert technical extension for your techno-functional business needs
Drop an email at crm@inogic.com
Service: https://www.inogic.com/services/
Tips and Tricks: https://www.inogic.com/blog/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors