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"
