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 s...
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.

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

      • GP85's avatar
        GP85
        New Member
        3 years ago

        Hi v-kalyj-msft, 

        There is no error. When step is evaluated I see in the status bar in the lower right corner it's is populating data from the reference table (like table 2) and it goes to a size of over 2gb. Would you know from the description what it is related to?