This topic contains 3 replies, has 2 voices, and was last updated by 
-
Posts
-
Okay, here’s a challenge about filtering a table based upon selected word(s) found in the strings of a column.
Consider the following list of restaurant businesses and their associated category(ies):
– Restaurant 1: Japanese
– Restaurant 2: Chinese
– Restaurant 3: Bar
– Restaurant 4: Japanese,Bar
– Restaurant 5: Japanese,Chinese,BarThe restaurant number is under a table column, the category value(s) are in a second column. I have attached a sample workbook, “TestYELPData.xlsm”. Part of the problem in the actual dataset is that there are over 200 unique category values. So, simply parsing each value into a separate column is not feasible.
If the user or report selects “Japanese”, then restaurants 1, 4, and 5 should be included. If both “Japanese” and “Bar” are the selected options, then restaurants 4 and 5 should be included.
I’m trying to work out the filtering logic and how it might work in PowerPivot or Power BI.
– In the Business table there is a column titled “Category” with comma-delimited values (e.g. “Japanese,Bar”)
– I parsed the values into a list of unique category values
– A category key was created for each business based upon the values in its Category column. The key is a simple concatenation of 0’s and 1’s. Since the edited category values are sorted, each value has a specific position within the key. For example, say “Bar” has the 2nd key position and “Japanese” holds the 5th position. Then a business with the category values of “Japanese,Bar” has a category key of “01001”. (The business’ order of the comma-delimited category values doesn’t matter.)
– So, I know if the user or report wants “Bar” and “Japanese” the Business table is simply filtered upon the CategoryKey where the 2nd position is “1” (for “Bar”) and the 5th position is “1” (for “Japanese”).Now, this approach may be totally unnecessary if a filter could be constructed using, say, IN() or CONTAINS(). However, I haven’t figured out the logic.
I’m hoping someone is smarter than I and knows the approach to filtering a table based upon single or multiple individual words within a string.
THANKS!
Ron
Ron,
You may be interested in taking a look at the Power Query course available here.
Within a few seconds after importing and merging data, I was able to detect with an anti-join that your categories list was missing Grocery, Food, Herbs & Spices, and Candy Stores.
After adding those categories, a solution was assembled based on the many-to-many relationship between businesses and categories. Power Query was used to extract the business categories and make a bridge table between business and category (using “Split Column” and unpivot).
A measure was then created based on Comprehensive Power Pivot Course by Rob Collie and Mastering DAX by Marco Russo/Alberto Ferrari to produce the desired effect (only businesses listed that meet all user selected criteria).
In the workbook with the pivot, I hid the column that contains the measure.
No VBA was required.
Tom
Hi Tom,
Thank you. Truly impressive what you know and what you did.
Yes, I will be signing up for the Power Query course. 🙂
Thank you!!
Ron
Ron,
Many thanks also for the challenge!
Attached is a “new and improved” version that also uses KPIs.
Other improvements include a step to remove (potentially) duplicate rows in Power Query for the business categories bridge table, plus a cleaner presentation. (Note to others following this topic: when removing duplicate rows in Power Query, it is important that all of the columns are selected before choosing this option, otherwise too many rows could be removed).
Also, did not mention in previous reply, but in the slicer settings I chose to not include items without any values (helpful at data refresh time for controlling the size of the slicer).
The forum ‘Power Pivot’ is closed to new topics and replies.