Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a large table (Table 1) containing a column with application names. These application names contain version numbers, etc. I would like to simplify this list of application names into groups of predefined strings.
I have created a seperate table (Table 2) with these predefined string values. The plan is to match each row in the application names column with the corresponding simplified string from Table 2. The matching is done by finding the simplified string within the application name.
I managed to create an excel formula for this, but cannot find a way how to use the "reverse wildcard" lookup method in Power BI.
Is this possible to do something similar in Power BI, or do I need to format the data before I pull it into Power BI?
Solved! Go to Solution.
Hi @Havrikahn
This calculated column should work.
New Column = VAR Matches = CALCULATETABLE( GENERATE( 'Table1', FILTER( Predefined, SEARCH( [Apps], [Applications], 1, 0 ) >0) ) ) RETURN CONCATENATEX( Matches, [Apps],",")
Here is a PBIX file you can download to test. This will also find and append when it discovers more than one match.
https://1drv.ms/u/s!AtDlC2rep7a-oi8WmrfnWoH031de
Hi,
This calculated column formula works fine as well
=FIRSTNONBLANK(FILTER(VALUES(keywords[Keywords]),SEARCH(keywords[Keywords],Applications[Application],1,0)),1)
Hi @Havrikahn
This calculated column should work.
New Column = VAR Matches = CALCULATETABLE( GENERATE( 'Table1', FILTER( Predefined, SEARCH( [Apps], [Applications], 1, 0 ) >0) ) ) RETURN CONCATENATEX( Matches, [Apps],",")
Here is a PBIX file you can download to test. This will also find and append when it discovers more than one match.
https://1drv.ms/u/s!AtDlC2rep7a-oi8WmrfnWoH031de
Very nice Solution!
Is there a way to filter more fitting matches to the Search?
Like I have
Predefined:
"Apple"
"Apple TV"
And now in the Applications, I have:
"Apple TV Remote"
It shows me:
"Apple, Apple TV" as match, but I only want the "better" match.
Would you please explain more about your solution, especially why column can be used as first argument of SEARCH?
Thanks.
Hi Hongjyan,
It's possible because you're pulling the entire column into the formula when you set the VALUES() term. This means you can also pull a different resultant column, like if you wanted to pull the vendor's name from the Keywords table based on matching the keywords[Keywords] column with Applications[Application] with a minor tweak; setting VALUES() to pull the entire table, then using SELECTCOLUMNS() to choose the field that you want to return:
VendorName = FIRSTNONBLANK(SELECTCOLUMNS(FILTER(VALUES(keywords),SEARCH(keywords[Keywords],Applications[Application],1,0)),"VendorName",[VendorName]),1)
Hope this helps,
David
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |