Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
EaglesTony
Helper V
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:

Key: 1234  AlignedToADepartment: Y

Key: 5678  AlignedToADepertment: N

 

How can i do this ?

 

Thanks in advance

5 REPLIES 5
dk_dk
Super User
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.

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

 

 

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.