Need "Y" or "N" flag on table involving 2nd table

[EaglesTony]

I have the following:

Table A:

Key: 1234

Key: 5678

Table B:

Key: 1234  Dept: Clothing

Key: 1234  Dept: Sporting Goods

What I want in Table A is:

Key: 1234  AlignedToADepartment: Y

Key: 5678  AlignedToADepertment: N

How can i do this ?

Thanks in advance

[dk_dk]

Hi @EaglesTony 

If I understand correctly, you want to check whether the keys in Table A exist in Table B, and if they do have the value Y, if not, N.

To achieve this you can add the following calculated column to Table A:

Exists in other tabe = 

IF(LOOKUPVALUE('Table B'[Dept],'Table B'[Key],'Table A'[Key],"N")="N","N","Y")

What happens here is LOOKUPVALUE will search Table B for the keys in table A, (return the Department name if it finds it) and return "N" if it does not find it. The IF statement around it is just to replace the department names with "Y".

I hope this helps, let me know if you have any questions.

[EaglesTony]

Where do you add a calculated column in PowerQuery or Dax ?

[audreygerred]

Calculated Columns are a DAX term. The syntax provided by @dk_dk is DAX. Power Query uses M code (Mash-Up).

[EaglesTony]

It changed slightly.

I was able to do a merge query on TableA and TableB.

Now TableA has the column from TableB called "Department", sometimes this value has a value sometimes it is null. As an example:

Table A:

Key: 1234 Dept: Sports

Key: 5678 Dept: Null

Now I need

Key: 1234 Sales: 10,000 Dept: Sports   SalesCol: 10,000   Non-SalesCol: 0

Key: 5678 Sales:      100 Dept: Null      SalesCol: 0   Non-SalesCol: 100

I eventually want to use these on a table as a value to sum by these 2 new columns

[dk_dk]

Ah yes, it works with table merge as well.

Then you could continue working in the Query Editor, and just add two custom columns.

For SalesCol "if Dept=null then 0 else Sales" and for Non-SalesCol "if Dept=null then Sales else 0"

If you do not like writing M code yourself you could probably also use the Custom Column by examples option in the query editor, and it will pick up the intended logic you want.

I hope this helps.