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...
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"