Forum Discussion

Ironpixel's avatar
Ironpixel
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

Merge 2 tables using similar data to match

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 NameTeam 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 NameTeam 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:

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

 

Ultimately I am hoping to get this:

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

 

Looking forward to any thoughts!
Thank you!

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Ironpixel , Try a new column in Dax Like this

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

  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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"

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Ironpixel
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      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!

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Ironpixel
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.