Hi All,
Thank you in advance for assistance/insights/etc.
Issue: What is the best way to "automatically/dynamically" "roll up/summarize/unify/group/aggregate" 100,000+ rows of data to "roll up" terms?
Background: I'm a marketer who works in Pay Per Click (PPC), who is starting their Power BI journey and wants to learn how to review and roll up terms to review performance to enable data-driven decisions. I've also been going through a Udemy PowerBI course and have learned a lot. I have yet to learn a solution for my issue.
I combed through several posts and didn't see a solution... I found something similar that only returns for one term... "Text Search" (https://community.powerbi.com/t5/Desktop/Text-Search/m-p/665714#M320171) which returns a Yes/No in a separate column. This won't help because I have several terms to dynamically 'roll-up'.
See the tables below for context that help illustrate my challenge:
Best, Michael
Table0 "Table.Column Descriptions" for the following tables that provide context to my issue.
| (Col.0.1) Table | (Col.0.2) Columns | (Col.0.3) Notes |
| Table0 | This Table | -- |
| Table1 | (Col.1.1) | Contains terms which may be whole or partial words and may contain spelling discrepancies |
| Table1 | (Col.1.1) | May contain partial words in 'find/match' to misspellings |
| Table1 | (Col.1.1) | The number of words to match to the roll-up depends on the Col.2 character count. |
| Table1 | | In some research I've seen this type of table called a:
* Transition Table (Where Col.1would labeled "From" & Col.2 would be labeled "To"
* Bridge Table (I don't believe Col.s need special names) |
| Table1 | (Col.1.2) | Contains the "roll-up" terms that I would like to "group/unify/aggregate" |
| Table1 | (Col.1.2) | I would like to see a summary of metrics based on these words |
| Table1 | (Col.1.2) | I would like to be able to search a data table through Col.1 'word iterations' and return Col. Summary words to enable summarized metrics |
| Table2 | (Col.2.1) | A subset from 100,000+ term and metric dataset |
| Table3 | (Col.3.1) | Example of roll-up table |
| Table4 | (Col.4.1) | Expansion of roll-up row that shows un-rolled up raw data (This table looks similar to an Excel pivot table. I understand table presentation may be different in PowerBI. Color is used to illustrate the difference between roll-up data and raw data.) |
| Table5 | (Col.5.1) | Solutions, I found online and implemented with limited success |
| Table5 | (Col.5.2) | Implementation |
| Table5 | (Col.5.3) | Result |
Table1
| (Col.1) | (Col.1.2) |
| abrasives | abrasives |
| abras | abrasives |
| sives | abrasives |
| rasiv | abrasives |
| brasi | abrasives |
| asive | abrasives |
| | + additional terms all at the same time |
| disc | disc |
| isc | disc |
| belt | belt |
| elt | belt |
Table2
| (Col.2.1) Example data terms | (Col.2.2) Impressions | (Col.2.3) Clicks | (Col.2.4) Cost | (Col.2.5) Revenue |
| abrasives 123 | 50 | 5 | 1.25 | 15 |
| abras 1x2 | 50 | 9 | 2.25 | 20 |
| sives belt | 75 | 7 | 1.75 | 50 |
| rasiv disc | 25 | 6 | 1.50 | 25 |
| brasi paper | 100 | 8 | 2.00 | 10 |
| asive flapdisc | 75 | 2 | 0.50 | 75 |
Table3
| (Col.3.1) Ability to roll up/unify data | (Col.3.2) Impressions | (Col.3.3) Clicks | (Col.3.4) Cost | (Col.3.5) Revenue |
| Abrasives | 375 | 37 | 9.25 | 195 |
| Disc | *** | ** | $# | $### |
| Belt | *** | ** | $# | $### |
| etc. | *** | ** | $# | $### |
Table4
| (Col.4.1) Ability to roll up/unify data | (Col.4.2) Impressions | (Col.4.3) Clicks | (Col.4.4) Cost | (Col.4.5) Revenue |
| Abrasives | 375 | 37 | 9.25 | 195 |
| abrasives 123 | 50 | 5 | 1.25 | 15 |
| abras 1x2 | 50 | 9 | 2.25 | 20 |
| sives belt | 75 | 7 | 1.75 | 50 |
| rasiv disc | 25 | 6 | 1.50 | 25 |
| brasi paper | 100 | 8 | 2.00 | 10 |
| asive flapdisc | 75 | 2 | 0.50 | 75 |
| Disc | *** | ** | $# | $### |
| Belt | *** | ** | $# | $### |
| etc. | *** | ** | $# | $### |
Table5
| (Col.5.1) Solution | (Col.5.2) Implementation | (Col.5.3) Result |
| IsFiltered is a "New Measure" (I found this example, but don't really understand what it is doing). | IsFiltered =
IF(
SUMX( 'tMatch',
Search(
'tMatch'[Match] ,
MAX( 'st'[Search term]),,0 )) > 0
,"True"
,"False") | I was able to use Filter where each row (Table1, Col.1) had a checkbox. I would like to review a 'rolled up' table of (Table1, Col.1.2) terms instead of having to make multiple selections from (Table1, Col.1) in order to see a 'roll-up'. |
| Edit Queries > Merge Query | I've tried combinations of "fuzzy" merge, unchecked "Ignore case", and "Transition table" with columns titled "From" and "To". | I typically manage to get errors. A text error directed me to set the Transition Table column types to text, but still had errors. |
| vlookup table | Relational tables | It wouldn't match right. I’m unsure why. This is why I made this post to seek advice/insight/direction/next steps/etc. |
| "manual ngram" strategy | split terms by space for review
Then also grouped terms 1&2, 2&3, 3&4, etc. looking for themes | |
| numerous other troubleshooting attempts | I'm learning PowerBI, and am working to learn more. | Still looking |
