Forum Discussion
How to filter matrix based on column data
I have a set of data like this: BaseColumn ColB ColC value 1 y n n value 2 y y y I want to filter to only value1 because BaseColumn=y while the other columns=n. The co...
Assuming your columns are fixed in number, the easiest way is to put following formula in a Custom column
if [BaseColumn]="y" and ([ColB]="n" or [ColC]="n") then "Yes" else "No"
Then filter on Yes on this Custom column and delete custom column after the filter.
Thanks for the reply. I'm not sure if this works based on the way the table is setup. Would it work if the he underlying data looks something like the table below?
| Name | ColType | Value |
| value1 | BaseColumn | y |
| value1 | ColB | n |
| value1 | ColC | y |
| value2 | BaseColumn | y |
| value2 | ColB | y |
| value2 | ColC | y |
Solution Excel workbook uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuST6bYrK9x8Xm5kO?e=GH4UsG
For scenario 2, use following code
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[ColType]), "ColType", "Value"), #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each if [BaseColumn]="y" and ([ColB]="n" or [ColC]="n") then "Yes" else "No"), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Yes")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"}) in #"Removed Columns"