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