Wildcard matching & returning a value

[analystict]

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).

[Zubair_Muhammad · Accepted solution]

@analystict

Try this one

ref_name =
MINX (
    FILTER ( refs, SEARCH ( refs[ ref], People[ ref], 1, 0 ) > 0 ),
    [ name ]
)

[Zubair_Muhammad]

@analystict

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 )

[analystict]

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 🙂

[Zubair_Muhammad · Accepted solution]

@analystict

Try this one

ref_name =
MINX (
    FILTER ( refs, SEARCH ( refs[ ref], People[ ref], 1, 0 ) > 0 ),
    [ name ]
)

[Anonymous]

Is it possible to do this in Power Query? It's taking very long to load in DAX or not loading at all...

[analystict]

Thanks a lot! Working great.

[Zubair_Muhammad · Accepted solution]

@analystict

[analystict]

Works well! Thank you 🙂