Forum Discussion

MikeC1's avatar
MikeC1
Frequent Visitor
8 years ago
Solved

Connecting two datasets

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

 

Need Help

3 Replies

    • MikeC1's avatar
      MikeC1
      Frequent Visitor
      8 years ago

      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's avatar
        v-chuncz-msft
        Community Support
        8 years ago

        MikeC1,

         

        Change it as follows.

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