Merge tables 2 columns matching 1

[GP85]

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

[v-yanjiang-msft · Accepted solution]

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]

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]

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]

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?

[PhilipTreacy]

Sorry @GP85 

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

Regards

Phil

[GP85]

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.

Portfolio	Security	Right look up on Portfolio	Left look up Security
Portfolio 1		Department 7	#N/A
Portfolio 2		Department 6	#N/A
Portfolio 9	Security 2	Department 5	Department 1
Portfolio 1	Security 1	Department 7	Department 7
Portfolio 11	Security 5	Department 3	Department 4
Portfolio 7		Department 3	#N/A
Portfolio 9	Security 4	Department 5	Department 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:

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

Best regards

GP

[PhilipTreacy]

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]

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:

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

Based on the below table:

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

End result from the Table 1 should be following:

Portfolio	Security
Portfolio 9	Security 2
Portfolio 9	Security 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