Connecting two datasets

[MikeC1]

Hi,

I'm trying to join two datasets, one of which has a unique identifier and second of which has a column that contains that unique identifier (in a string along with other information). 

This is what it looks like: 

Dataset 1

Unique ID     Name     Info

Z01               ---          ---

Z03               ---          ---

Z04               ---          ---

Z05               ---          ---

Dataset 2

Unique ID        Name     Info

Z01+Z02          ---          ---

Z01+Z04          ---          ---

Z04+Z05          ---          ---

Z01+Z04+Z05 ---          ---

Is there any way of joining the two together? I considered splitting the column in dataset 2 but I'm not sure if that'll work - especially if I have a large number of unique IDs. 

Thanks for your help

[v-chuncz-msft · Accepted solution]

@MikeC1,

You may take a look at the post below.

http://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/td-p/110108

[v-chuncz-msft · Accepted solution]

@MikeC1,

You may take a look at the post below.

http://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/td-p/110108

[MikeC1]

Thank you, 

I'm not very good with the m language - are you able to help me out further? How can I integrate this into my report? Through 'Advanced Editor' within the 'Query Editor'? 

Also assuming that my table 1 was "Dog", table 2 was "Cat", unique identifier in both of the tables was "Unique ID", is this what it'd look like? 

let
    Table1 = Table.Buffer(Dog),
    Table2 = Table.Buffer(Cat),
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each Text.Contains(Cat[Unique ID], Comparer.OrdinalIgnoreCase))),
    #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin")
in
    #"Expanded RelativeJoin"

Again, thanks for your help. 

[v-chuncz-msft · Accepted solution]

@MikeC1,

Change it as follows.

each Text.Contains([Unique ID], Earlier[Unique ID],