cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

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

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:

How can i do this ?

5 REPLIES 5
Super User

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.

Proud to be a Super User!

Helper V

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

Super User

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

Proud to be a Super User!

Helper V

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

Super User

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.

Proud to be a Super User!

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors