I'm using PowerQuery and Power Pivot to analyze some data. I would like to create a slicer based on data from multiple columns. Typically slicers are tied to a single column.
The below table is a simple example I contrived to illustrate what I'm trying to do. I'd like to create a slicer, natively in Excel or using VBA, that allows me to slice this data by borough (Brooklyn, Manhattan, etc.). This would be straightforward if every employee worked in one and only one location, but some employees (Smith and Jessop) work in multiple.
| Employee | Brooklyn | Manhattan | Bronx | Staten Island | Queens |
|---|---|---|---|---|---|
| Mary Shields | 1 | ||||
| Brian Jones | 1 | ||||
| John Smith | 1 | 1 | |||
| Martin Casey | 1 | ||||
| Evelyn Jessop | 1 | 1 |
I could, but really don't want to, have multiple rows per employee, each tied to a single borough, but this is an ugly solution and undesirable in my real analysis which is much more complex than the above. Thanks in advance!
Edit: I should add that my analysis already utilizes a number of slicers so, while the above could probably be accomplished with filters or some other mechanism, I'd rather keep it consistent with what I have for the benefit of the consumers of said analysis.
21 Answer
To be able to filter as you want, you need to multiply rows, the good news is that you can do it in power query, here is the code for that:
let Source = Table1, #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Employee"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "location"}}), #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Employee"}, #"Renamed Columns", {"Employee"}, "Renamed Columns", JoinKind.LeftOuter), #"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"location"}, {"EmployeeLocations"})
in #"Expanded Renamed Columns"few example screenshots:
Update for counting entries:
To calculate totals, you need to load your data by selecting "add to data model" option
Now you can calculate "distinct count" of employees, which will do what you need.
Or you can do the same for locations too for calculating row totals, unfortunately there is no way to calculate correctly both totals at the same time.
5
