Two Table matching data

[stribor45]

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

[AlienSx]

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
    )

[m13eam]

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.

[j_ocean]

1. Start in B, merge in A on both caller ID and Agent Name. 
2. Expand table A
3. Add column(s) with logical checks returning desired values
4. Remove duplicative columns.

[stribor45]

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?

[stribor45]

@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]))

[spinfuzer]

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

[stribor45]

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? 

[spinfuzer]

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.

[stribor45]

@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]
           
        )

[spinfuzer]

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.

[stribor45]

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

[m13eam]

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:

1. 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
2. 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
3. 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
4. From Table A, merge (Left outer join) Table B1 on call id, agent name and agent age. Expand to return the custom column
5. From Table A, merge (Left outer join) Table B2 on call id and agent name. Expand to return the custom column
6. From Table A, merge (Left outer join) Table B3 on call id. Expand to return the custom column
7. 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

[stribor45]

@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])

[stribor45]

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