Forum Discussion
Filter one colunm by another two columns
HI,
I have a table as sample below
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
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,There will be an Error,
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")
Thereafter you can Select False in the Filter
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 [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
2 Replies
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,There will be an Error,
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")
Thereafter you can Select False in the Filter
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 [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
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))
