Forum Discussion
Complex: Filtering data by name
Hi, I have a list of names in the filter and if I select a name from the filter then all the records with the selected name should be filtered out. For example, in a table there is a field ca...
I went ahead and did this using power query, so I attached the pbix file below. But I went off using this example:
| Project | Fruits |
| A | Apple, Pear, Lime |
| B | Apple, Orange, Grape, Strawberry |
| C | Grape |
| D | Lime, Strawberry |
After loading that table into PowerQuery:
- Split the "Fruits" column by delimiter and to new rows, not columns. giving this table
- Group this new table by project and aggregate all data
- Add a new column wiht this code
List.Accumulate( [All Data][Fruits],"", (state,current) => state &"," & current )
which produces this column:
Expand that table out
Do some misc clean up and you get this final table
Then just to make the data model complete, created a dimFruits and DimList table which would be used to filter the above table
Final output
File:
