Forum Discussion
Solved
Filter a table by data that has multiple options
Hi, I am creating a small software catalogue of software used across the organisation and by which departments data is all in one table softwarename dep1 dep2 dep3 testsoftware1 YES NO ...
Hello @Kerslaing ,
The best option is in the query editor to despivot your departments and get a table with 3 columns:
- SoftwareName
- Attribute (Department Names)
- Value (yes/no)
You can then rename the columns and use the attribute column in the slicer to check the full code for the power query and the PBIX file below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLinOTyspTyxKNVTSUYp0DQaSfv4QIlYHVYURTA6iDESiKzFGNQSbEhOC9pgiyUHNiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [softwarename = _t, dep1 = _t, dep2 = _t, dep3 = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"softwarename"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Department"}, {"Value", "HasSoftware"}}) in #"Renamed Columns"Then filter the visualizations by the value column.
Hello @Kerslaing ,
The best option is in the query editor to despivot your departments and get a table with 3 columns:
- SoftwareName
- Attribute (Department Names)
- Value (yes/no)
You can then rename the columns and use the attribute column in the slicer to check the full code for the power query and the PBIX file below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLinOTyspTyxKNVTSUYp0DQaSfv4QIlYHVYURTA6iDESiKzFGNQSbEhOC9pgiyUHNiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [softwarename = _t, dep1 = _t, dep2 = _t, dep3 = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"softwarename"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Department"}, {"Value", "HasSoftware"}})
in
#"Renamed Columns"
Then filter the visualizations by the value column.
