Query; conditional column across tables

[MrPatrick]

I have two tables

table 1 "statuses"

status | complete | customer_contact
sale   | Y        | Y
nosale | N        | Y
other  | N        | N

table 2 "list"

status | DMC
sale   |
sale   |
nosale |
sale   |

table 1 has 40 rows, one for each status, there are no duplicates; table 2 has ~1000000 rows that could be any one of those 40 statuses.

In the query editor* I want to populate DMC in table 2 with a Y or N where the 'status' matches the 'status' in table 1 and complete and customer_contact both = "Y" (just sale in the example)

* I realise that perhaps doing this in M isn't the right approach, is this easier in DAX?

I had expected to use a conditional column but seemingly that doesn't work across tables?

The following works if I add it in table 1 'statuses'

= Table.AddColumn(statuses, "DMC", each if [status] = [status] and [customer_contact] = "Y" and [completed] = "Y" then "Y" else "N")

(I realise that in the above example the first clause is not not needed but i've got it in there as it demonstrates that the logic works when in table 1)

if I modify that and add it to table 2 nothing happens.

= Table.AddColumn(list, "DMC", each if list[status] = statuses[status] and statuses[completed] = "Y" and statuses[customer_contact] = "Y" then "Y" else "N")

I'm presented with a grey screen that just shows the query. I've tried to go around this in many ways and had a whole bunch of different errors but this is where I'm at, and I just can't get anything to work, the above is the clearest way to describe what I'm hoping to do, I think.

Am I just going about this the wrong way?

I'm thinking given the difficulties I'm having it'd be better to leave the DMC column in the statuses, as that works, and then consume this in DAX, but I've tried that too and can't get it to work either. In the spirit of learning though I'd like to understand if this is possible or not.

[tex628]

Are there no status duplicates in the "statuses" table?

[MrPatrick]

No, these are all unique entries.

[tex628 · Accepted solution]

Wonderful, 

In that case keep the first column you created in the Statuses table and then create a merge between your two tables. Merge on status and then expand the column that you previously created. 

Top right in the PowerQuery Editor

I think this should give you the correct result, 

/ J

[MrPatrick]

Great thank you, working as I expected.

Frustrating, I'd managed to get the table appended like this somehow, I must have manually written a merge in the function bar (!!) but didn't realise I was just one expand away from what I needed.