Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ironpixel
Employee
Employee

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!

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Ironpixel , Try a new column in Dax Like this

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

View solution in original post

CNENFRNL
Community Champion
Community Champion

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"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
v-yuaj-msft
Community Support
Community Support

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:

v-yuaj-msft_0-1606117330214.png

 

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
Community Champion
Community Champion

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"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@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
Super User
Super User

@Ironpixel , Try a new column in Dax Like this

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

@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.