- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Two Table matching data
I have Table A and Table B
Table A has columns call id , agent name, agent age
Table B has columns call id, agent name, agent age and some other columns
How would I find out the following
the first step would be to create custom columns
second step -
Is there a row in Table B that same same call id, agent name and agent age as current row in Table A and if so put 10 in custom column for this row
is there a row in Table B that have same call id, and agent name as current row and if so put 5 on custom column for this row
id there a row in Table B that same same call id as current row and if so put 1 in custom column for this row
Any help would be much apprreciated
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

hello, @stribor45
distinctB = Table.Distinct(TableB[[call id], [agent name], [agent age]]),
lookupB =
[ten = List.Buffer(Table.ToRecords(distinctB)),
five = List.Buffer(List.Distinct(Table.ToRecords(distinctB[[call id], [agent name]]))),
one = List.Buffer(List.Distinct(distinctB[call id]))],
modA = Table.AddColumn(
TableA, "Custom column",
(x) =>
if List.Contains(lookupB[ten], x) then 10 else
if List.Contains(lookupB[five], Record.SelectFields(x, {"call id", "agent name"})) then 5 else
if List.Contains(lookupB[one], x[call id]) then 1 else null
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This is much better than my suggestion. Meets requirements of original question perfectly and is both quicker to set up and performs better than my suggestion. Thanks for sharing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Start in B, merge in A on both caller ID and Agent Name.
- Expand table A
- Add column(s) with logical checks returning desired values
- Remove duplicative columns.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I cant do it that way because I don't know what the file looks like ahead of time. sometimes there might be a match between the caller ID and last name and sometimes a caller id and first name. the example above with Sienfeld characters is just a basic example. Is there a way you can help with the formula above?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@m13eam I am having some difficulty with this code. I cant figure out why it is giving me this error "Expression.Error: The field 'First Name' of the record wasn't found"
This is the table A and table B. i am trying to find whether there is first name from table A in Table B and if there is last name in same row there as well
and this is the code that is giving me some troubles
not Table.IsEmpty(Table.SelectRows(#"Table B", each [First] = [First Name] and [Last] = [Last Name]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You cannot use "each" twice one line of M code.
each is the same as (_) =>. [First Name] is the same as _[First Name]. It just leaves out the extra underscore.
Instead of an underline, you need to replace the underline with anything you want.
not Table.IsEmpty(Table.SelectRows(#"Table B", (anything) => anything[First] = [First Name] and anything[Last] = [Last Name]))
Now columns with the anything prefix will refer to #"Table B" and any columns without a prefix will refer to Table A
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This seems to work but to be honest I am kind of lost. By doing (anything) => is anything argument of this function which is Table A?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Your full formula looks something like this
Table.AddColumn(
priorStep,
"Custom",
each not Table.IsEmpty(
Table.SelectRows(#"Table B", (anything) => anything[First] = [First Name] and anything[Last] = [Last Name])
)
)
That is really the same as
Table.AddColumn(
priorStep,
"Custom",
(_) => not Table.IsEmpty(
Table.SelectRows(#"Table B", (anything) => anything[First] = _[First Name] and anything[Last] = _[Last Name])
)
)
You are assigning a variable to each table. Your "priorStep" table's name is "_".
Your Table B's name is "anything". Power Query has to know which table's column you are refering to. (anything) => lets you know that you have named the table "anything". You could have made it (zzz) => and you would write zzz[First] and zzz[Last] instead.
each just syntax sugar for (_) => and _[First Name] is the equivalent of [First Name]. each is made to simplify the formulas where it is possible.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@spinfuzer I am trying to wrap my head around this. I have another for practice where I am trying to create a custom column in Table A which should be concatenation of name from Table A and Table B.
My code is this but it returns list of all the names from Table B which is giving me errors because B[Nm] is list of all the names from Table B. How do I iterate over each and concatinate it with name from Table A?
List.Select(
B[Nm],
(anything) =>
anything[Nm] & " " & [Names]
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You mean you want to take all the names from Table B and then concatenate that with all the names in table 8, ending up with 81 or more records?
if so it is
List.Transform(
B[Nm],
(anything) =>
anything & " " & [Names]
)
B[Nm] is a List of Names. I believe you are trying to Transform a List of Names. We are giving each element of this list the name "anything". Each element is a text value in this case. You cannot use the [] notation because it is NOT a table. "anything" is referring to the current text value in the list of names and [Names] is referring to Table A's current Name.
When you are using the () => notation, you are going 1 level lower of the object. Table gets broken down to each row or column depending on the formula. List gets broken down to each element of the list.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

no i was hoping to concatinate each name from A to the name in B so it would be 19 records
Mcneil Vaughn
Norman Denny
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi - I have a suggestion, but I fear it may not provide great performance if the tables contain many rows or transformations. Nevertheless, how about:
- Duplicate (or Reference) Table B (call it B1), remove columns other than call id, agent name and agent age. Then, remove duplicates. Then, add a custom column called "Output" with a value 10
- Duplicate (or Reference) Table B (call it B2), remove columns other than call id, and agent name. Then, remove duplicates. Then, add a custom column called "Output" with a value 5
- Duplicate (or Reference) Table B (call it B3), remove columns other than call id. Then, remove duplicates. Then, add a custom column called "Output" with a value 1
- From Table A, merge (Left outer join) Table B1 on call id, agent name and agent age. Expand to return the custom column
- From Table A, merge (Left outer join) Table B2 on call id and agent name. Expand to return the custom column
- From Table A, merge (Left outer join) Table B3 on call id. Expand to return the custom column
- Add a custom column within Table A with this formula "if [B1.Output] <> null then [B1.Output] else if [B2.Output] <> null then [B2.Output] else if [B3.Output] <> null then [B3.Output] else null"
I think that'd work! Let me know if you have any questions.
Good luck!
Matt
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@m13eam I am going to try your solution shortly but before I do I want to try mine. I have a bunch of these statements and each produces Boolean true or false. As you can see I have four of them and I want to somehow group them in big IF statements that would basically say if (all of these are true show "match") else "no match". I think this syntax is throwing me off in power query
not List.IsEmpty(List.FindText(#"Table B"[Call_ID], [callid])) and
not List.IsEmpty(List.FindText(#"Table B"[Last], [Last Name])) and
List.Contains(#"Table B"[AGNT], [Agent]) and
List.Contains(#"Table B"[Age], [ages])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I was able to resolve this logic for if and else statements so I am good there. I will let you know about your solution

Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
13 | |
9 | |
9 | |
8 | |
7 |
User | Count |
---|---|
13 | |
11 | |
9 | |
6 | |
6 |