cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ironpixel
Microsoft
Microsoft

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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"

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"

View solution in original post

@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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!