Merge 2 tables using similar data to match

[Ironpixel]

The title may be strange, but the essence will hopefully become clear.

I am using Power BI - Tranform Data so Power Query

I have two tables:

Table 1

User Name	Team Number
John	/13575/685348/896913/38683/65834/43866
Fred	/13757/685348/6483/68436/96837/31838/3183
Bill	/13757/685348/6483/68436/38138/431689/6831/13813

Table 2

Team Name	Team Number
Team A	/13575/685348/896913
Team B	/13575/685348/13864
Team C	/13757/685348/6483/68436/96837
Team D	/13575/685348/18683/13556
Team E	/13757/685348/6483/68436/68318

I am looking to match the User Name to the Team Name through the Team Number.

Unfortuantely I the Team number I Get from the User might be longer as they belong to a nested team.

Its easy to Merge Querries using Left Outer to match these up when the data is the same.

How can I get these to match when I only want to use the first part of the Team Number in Table 2?
I tried useing fuzzy match but possibly due to the depth of the Team Numbers it didn't work to well.

What I am getting is this:

Name	Team Number	Team Name
John	/13575/685348/896913/38683/65834/43866	null
Fred	/13757/685348/6483/68436/96837/31838/3183	Team C
Fred	/13757/685348/6483/68436/96837/31838/3183	Team E
Bill	/13757/685348/6483/68436/38138/431689/6831/13813	Team C
Bill	/13757/685348/6483/68436/38138/431689/6831/13813	Team E

Ultimately I am hoping to get this:

Name	Team Number	Team Name
John	/13575/685348/896913/38683/65834/43866	Team A
Fred	/13757/685348/6483/68436/96837/31838/3183	Team C
Bill	/13757/685348/6483/68436/38138/431689/6831/13813	Team E

Looking forward to any thoughts!
Thank you!

[amitchandak · Accepted solution]

@Ironpixel , Try a new column in Dax Like this

maxx(filter(Table2, search(table2[Team Number], table1[eam Number],,0)>0),Table2[Team Name])

[Anonymous]

Hi @Ironpixel ,

Based on your description, you can create a measure or a calculated column as follows.

1. Measure:

Measure_match =

MAXX(

    FILTER(

        'Case1_2',

        SEARCH(

            'Case1_2'[Team Number],

            MAX('Case1-1'[Team Number]),

            ,

            0

            )>0

            ),

            [Team Name]

            )

1. Calculated column:

Match_column =

SWITCH(

    TRUE(),

    SEARCH("896913",'Case1-1'[Team Number],1,0)>0,"Team A",

    SEARCH("13864",'Case1-1'[Team Number],1,0)>0,"Team B",

    SEARCH("96837",'Case1-1'[Team Number],1,0)>0,"Team C",

    SEARCH("13556",'Case1-1'[Team Number],1,0)>0,"Team D",

    SEARCH("38138",'Case1-1'[Team Number],1,0)>0,"Team E",

    "No"

)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL · Accepted solution]

Hi, @Ironpixel , you might want to try such a solution,

let
    Lookup = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc47CoAwEATQq8jWgSXsN6W/G9iFFBaW3r81opIiYDcMj2Fyhu3Yz2GEABhJTFBdiB09aYoEJbxi6kQkV25gfoCJfUDZqWYmxaRO1uzSj3kVdyXa2Po3SV4PQCkX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Team Name" = _t, #"Team Number" = _t]),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcuxCgJBDIThd0l9EMIk2dnWwsJXWLY7wYPjBN+/MJ7Y2gQy/N8Ycns+DllEDdFCkwGnsmc3KJiEZhCuXk/KXIZcX/f1K1q0n0j/lHSk9kJNYQTPe6LLtu//EGiVOyzZa4JVWZPM+QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User Name" = _t, #"Team Number" = _t]),

    Matchup = Table.AddColumn(
        Source, "Team Name",
        each List.Accumulate(
            Table.ToRecords(Lookup),
            {[Team Number], {}},
            (s, c) => if Text.Contains(s{0}, c[Team Number], Comparer.OrdinalIgnoreCase) then {s{0}, s{1}&{c[Team Name]}} else s
        ){1}
    ),
    #"Expanded Team Name" = Table.ExpandListColumn(Matchup, "Team Name")
in
    #"Expanded Team Name"

[Ironpixel]

@CNENFRNL Still trying to get this to work.  Unfortuantley my Advanced Editor knowledge is a little behind, but hoping to be able to pull it off.

Thanks!

[amitchandak · Accepted solution]

@Ironpixel , Try a new column in Dax Like this

maxx(filter(Table2, search(table2[Team Number], table1[eam Number],,0)>0),Table2[Team Name])

[Ironpixel]

@amitchandra  - This is a great solution in DAX!
I got it working.  If I can't get it working in Power Query I will use this.
Thank you!