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.
Hi,
Basically, I have a table named "people" with different records and a record named "ref"
id | country | age | ref
1 | China | 25 | a32f134fnaq?ref=site
2 | USA | 22 | a32f134fnaq
3 | China | 28 | 31444cp2x?ref=google
4 | China | 33 | a32f1dddd?ref=none
And another table named "refs"
id | name | ref
1 | john | a32f134fnaq
2 | jack | 31444cp2x
3 | sam | a32f1dddd
A column has been created (assume it's called "ref_name" that uses VLOOKUP and returns the "name" column from "refs" by looking it up according to "ref" from the table "people".
Obviously, it does not work as there are parameters (after the ?), and it would only work if the inputs are precise (e.g. the bold record).
How can I manage to overcome this? So that even if there is something like "31444cp2x?ref=google", it will check if "ref" in "refs" conatains 31444cp2x and return 'name' (aka approximate match or something like that).
Solved! Go to Solution.
Try this one
ref_name = MINX ( FILTER ( refs, SEARCH ( refs[ ref], People[ ref], 1, 0 ) > 0 ), [ name ] )
Try this column
ref_Name = VAR TextbeforeQM = LEFT ( People[ ref], VAR mylen1 = FIND ( "?", People[ ref], 1, 0 ) RETURN IF ( mylen1 = 0, LEN ( People[ ref] ), mylen1 - 1 ) ) RETURN LOOKUPVALUE ( refs[ name ], refs[ ref], TextbeforeQM )
Thanks for your reply 🙂
Thing is, "?blablabla" is not always the case.
It could also be ref=facebook?a32f134fnaq
or
a_id=a32f134fnaq
Is there a workaround for that?
Thanks 🙂
Try this one
ref_name = MINX ( FILTER ( refs, SEARCH ( refs[ ref], People[ ref], 1, 0 ) > 0 ), [ name ] )
Is it possible to do this in Power Query? It's taking very long to load in DAX or not loading at all...
Thanks a lot! Working great.
Works well! Thank you 🙂
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |