Forum Discussion

GP85's avatar
GP85
New Member
3 years ago
Solved

Merge tables 2 columns matching 1

Hi,

Sorry for sily question but I am new to the topic. I am trying to merge 2 tables based on a condtion that 2 columns from one table must be match with another colum from second table. I can add some helping columns and then filter only those that have specific value I am looking for but maybe there a bettter way?

Hope this makes sense.

Thanks

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi GP85 ,

    According to your description, here's my solution. Add a step in Table1 Advanced editor:

    #"New"=let 
    filter=Table.SelectRows(Table2,each [Department]="Department 1")
in
    Table.SelectRows(#"Changed Type",each List.Contains(filter[Portfolio],[Portfolio]) or List.Contains(List.RemoveNulls(filter[Security]),[Security]))

    Result:

    Here's the whole M syntax:

    Table2:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvKknLz8nMVzBU0lFySS1ILCrJTc0rUTAHcoNTk0uLMksqgXKxOshqjVDVmgG5aCqMUVUYYqowwVQBt88ITa0pqlojTNPMCKowR1VhjKnCgqAKS1QVpshuNkZTa2hAMAgMDQnaaIgW1CbIVpqiK8YS6nDFJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Portfolio = _t, Department = _t, Security = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Portfolio", type text}, {"Department", type text}, {"Security", type text}})
in
    #"Changed Type"

    Table1:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvKknLz8nMVzBU0lFSUIrVQRYzwiJmCRQLTk0uLcosqQQqQJU0RJY0RJdEkTVFkzUnZJcJmqQFFg2GBhDBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Portfolio = _t, Security = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Portfolio", type text}, {"Security", type text}}),
    #"New"=let filter=Table.SelectRows(Table2,each [Department]="Department 1")in
    Table.SelectRows(#"Changed Type",each List.Contains(filter[Portfolio],[Portfolio]) or List.Contains(List.RemoveNulls(filter[Security]),[Security]))
in
    #"New"

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi GP85 ,

    According to your description, here's my solution. Add a step in Table1 Advanced editor:

    #"New"=let 
    filter=Table.SelectRows(Table2,each [Department]="Department 1")
in
    Table.SelectRows(#"Changed Type",each List.Contains(filter[Portfolio],[Portfolio]) or List.Contains(List.RemoveNulls(filter[Security]),[Security]))

    Result:

    Here's the whole M syntax:

    Table2:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvKknLz8nMVzBU0lFySS1ILCrJTc0rUTAHcoNTk0uLMksqgXKxOshqjVDVmgG5aCqMUVUYYqowwVQBt88ITa0pqlojTNPMCKowR1VhjKnCgqAKS1QVpshuNkZTa2hAMAgMDQnaaIgW1CbIVpqiK8YS6nDFJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Portfolio = _t, Department = _t, Security = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Portfolio", type text}, {"Department", type text}, {"Security", type text}})
in
    #"Changed Type"

    Table1:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsgvKknLz8nMVzBU0lFSUIrVQRYzwiJmCRQLTk0uLcosqQQqQJU0RJY0RJdEkTVFkzUnZJcJmqQFFg2GBhDBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Portfolio = _t, Security = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Portfolio", type text}, {"Security", type text}}),
    #"New"=let filter=Table.SelectRows(Table2,each [Department]="Department 1")in
    Table.SelectRows(#"Changed Type",each List.Contains(filter[Portfolio],[Portfolio]) or List.Contains(List.RemoveNulls(filter[Security]),[Security]))
in
    #"New"

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • GP85's avatar
      GP85
      New Member
      3 years ago

      Hi, 

      Thank you for your help. This works just as expected however on a larger data set (150k rows) it cannot complete the task. Is this solution good for larger data set or just something wrong with my data?

      Thanks again

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi GP85 ,

        I think the formula is also good for larger dataset. Did you get a error? If so, what's the error message.

         

        Best Regards,
        Community Support Team _ kalyj

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    Hi GP85 

     

    You'll probably need to do some kind of table join but hard to say exactly without seeing your data and knowing exactly what the result you want is.  Can you please provide both?

     

    Regards

     

    Phil

    • GP85's avatar
      GP85
      New Member
      3 years ago

      Hi PhilipTreacy 

       

      Thanks for your message. 

      From this table I would like to keep only records where either Portfolio or Security belongs to Department 1:

      PortfolioSecurity
      Portfolio 1 
      Portfolio 2 
      Portfolio 9Security 2
      Portfolio 1Security 1
      Portfolio 11Security 5
      Portfolio 7 
      Portfolio 9Security 4
      Portfolio 8 
      Portfolio 10 

       

      Based on the below table:

       

      PortfolioDepartmentSecurity
      Portfolio 1Department 7Security 1
      Portfolio 2Department 6 
      Portfolio 3Department 1 
      Portfolio 4Department 1Security 2
      Portfolio 5Department 2 
      Portfolio 6Department 2 
      Portfolio 7Department 3 
      Portfolio 8Department 3 
      Portfolio 9Department 5Security 3
      Portfolio 10Department 1 
      Portfolio 11Department 3 
      Portfolio 12Department 4Security 5
      Portfolio 13Department 1Security 4

       

      End result from the Table 1 should be following:

      PortfolioSecurity
      Portfolio 9Security 2
      Portfolio 9Security 4
      Portfolio 10 

       

      Portfolio 9 holging Securities 2 and 4 that belong to Department 1

      Portfolio 10 that belongs to Departmenet 1.

       

      Hope that helps.

       

      Many thanks

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    Sorry GP85 

     

    I'm not following the logic that comes up with your result table.

     

    Regards

     

    Phil

    • GP85's avatar
      GP85
      New Member
      3 years ago

      hi Phil,

       

      A condition to keep record should be that either Portfolio or Security belong to Department 1.

       

      In terms of helper columns (lookups in Excel) I can only explain in the following way. Adding 2 columns to Table 1.

       

      PortfolioSecurityRight look up on PortfolioLeft look up Security
      Portfolio 1 Department 7#N/A
      Portfolio 2 Department 6#N/A
      Portfolio 9Security 2Department 5Department 1
      Portfolio 1Security 1Department 7Department 7
      Portfolio 11Security 5Department 3Department 4
      Portfolio 7 Department 3#N/A
      Portfolio 9Security 4Department 5Department 1
      Portfolio 8 Department 3#N/A
      Portfolio 10 Department 1#N/A

       

      Then filter out those not "Department 1" based on the 2 Helper Ccolumns:

       

      PortfolioSecurityRight Look up on PortfolioLeft Look up Security
      Portfolio 9Security 2Department 5Department 1
      Portfolio 9Security 4Department 5Department 1
      Portfolio 10 Department 1#N/A

       

      Best regards

       

      GP