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
nniphadk
Frequent Visitor

Column with multiple conditions

Good Morning 🙂
I am struggling with creating a column/conditional column
Conditions: 

● BD: TR Type = CRD, TR Code = 123, BK Trx # = 41, TR Text Does Not Contain "DCD"
● MD: TR Type = CRD, TR Code = 123, BK Trx # = 41, TR Text Contains "DCD"
● LD: TR Type = CD, TR Code = 125, BK Trx # = 54
● BMDS: TR Type = CD, TR Code = 123, BK Trx # has more than 6 digits (excl. leading zeros)

 

I have tried to do a custom column in Power Query is there any other method to achieve what I am trying to do.

2 REPLIES 2
nniphadk
Frequent Visitor

TR TypeTRCodeBKTrx #TR Text
CRD4561.91E+13ST
CRD12341DCD
CRD12341DCD
CRD1231DCD
CRD12341DCD
DBT1231DCD
DBT12341DCD


Here is a sample data set.

Greg_Deckler
Super User
Super User

@nniphadk That would be one long, nasty if statement in Power Query. DAX might be cleaner:

Column = 
  SWITCH(TRUE(),
    [TR Type] = "CRD" && [TR Code] = 123 && [BK Trx #] = 41 && NOT(CONTAINSSTRING([TR Text], "DCD")), "BD",
    [TR Type] = "CRD" && [TR Code] = 123 && [BK Trx #] = 41 && CONTAINSSTRING([TR Text], "DCD"), "MD",
    [TR Type] = "CRD" && [TR Code] = 123 && [BK Trx #] = 54, "LD"
  )

If you can provide sample data as text in a table, could be more complete with the solution. The last case is going to be similar although the leading zeros could be an issue. Are there a constant number of leading zeros or is it variable?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.