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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
acerNZ
Helper III
Helper III

Multi Column Conditional Custom column

Hi Experts

I have two tables, 

Table 1 :Enroll Status with  columns

IDEOIEnrolled

Table 2: Payment Portal

IDPayment

 

All I have to do is create visualization and report the status baed on  EOI (Expression of Interest,  Payment confirmation, Enrolled Status) and give data

 

1. Do we need to add custom column or can we accomplish the same best way directly in visualizations, I need total EOI, total Enrolled, etc 

2. What I did, I added a custom column called it "Status" and in the custom Column wanted to do the following code 

= IF(Related(EnrollStaus[EOI]) = "NULL", "Not Interested",
IF(Related(Payment[payment]) <> "NULL", and (Payment[Payment]) <> "NULL" and EnrollStaus[EOI]<> "NULL", "Enrolled" ) etc 

It was throwing up error, so debug, just used the following code 

IF(Related(EnrollStaus[EOI]) = "NULL", "Not Interested") to try and it has issues with my IF .. please can you explain me why and what is best way to handle

Table 1: Enroll Status

2020-10-13_10h41_45.png

Table 2: Payment Protal 

2020-10-13_10h42_36.png

Logic to get Status column 

2020-10-13_10h43_44.png

This is the error when I tried just one condition:

2020-10-13_10h28_28.png

1 ACCEPTED SOLUTION

Hi @acerNZ ,

 

Firstly, please change the columns data type from text to date. They will change "NULL"  or "Null" to blank values automatically.

 

In Power Query, you could try the M code: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIL9fGBUbE60UpGQI6xvqGhvpGBkQGQbQpng2SNUUTQZU0Q5oH5pmiq4ZaYATlm2CTMgRxzJAlzFPMtsLjXEtnOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EOI = _t, Enrolled = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EOI", type date}, {"Enrolled", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Payment", each (let currentID = [ID] in Table.SelectRows(#"Payment Portal", each [ID] = currentID)){0}[Payment]),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each if [EOI] = null then "Not Interested" else if [Payment] = null then "Yet to Enroll" else "Enrolled")
in
    #"Added Custom"

v-xuding-msft_0-1602570468653.png

 

In Report view, please do this:

Column = 
SWITCH (
    TRUE (),
    [EOI] = BLANK (), "Not Interested",
    RELATED ( 'Payment Portal'[Payment] ) = BLANK (), "Yet to Enroll",
    "Enrolled"
)

 

For more details, please download the attachment to have a try.

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
acerNZ
Helper III
Helper III

This one worked in the REPORT view, by adding column,

1. Cannot I do this in Query Editor ? 

2. Next: I am not able to combine say If (EOI <> "NULL" || "Null" AND if payment portal[payment] <> "NULL" || "Null", "Enrolled") etc? 

2020-10-13_11h23_08.png

Hi @acerNZ ,

 

Firstly, please change the columns data type from text to date. They will change "NULL"  or "Null" to blank values automatically.

 

In Power Query, you could try the M code: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIL9fGBUbE60UpGQI6xvqGhvpGBkQGQbQpng2SNUUTQZU0Q5oH5pmiq4ZaYATlm2CTMgRxzJAlzFPMtsLjXEtnOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EOI = _t, Enrolled = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EOI", type date}, {"Enrolled", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Payment", each (let currentID = [ID] in Table.SelectRows(#"Payment Portal", each [ID] = currentID)){0}[Payment]),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each if [EOI] = null then "Not Interested" else if [Payment] = null then "Yet to Enroll" else "Enrolled")
in
    #"Added Custom"

v-xuding-msft_0-1602570468653.png

 

In Report view, please do this:

Column = 
SWITCH (
    TRUE (),
    [EOI] = BLANK (), "Not Interested",
    RELATED ( 'Payment Portal'[Payment] ) = BLANK (), "Yet to Enroll",
    "Enrolled"
)

 

For more details, please download the attachment to have a try.

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CNENFRNL
Community Champion
Community Champion

Hi, @acerNZ , of coz you can use such logical expressions in PQ, but with native logical operators of M language, i.e. or/and/not (case-sensitive). Pls refer to official doc for more details.

https://docs.microsoft.com/en-us/powerquery-m/operators


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL  Yes you are right and I got the syntax right

we need to use if, then and else.. all good.

Please can you help me as I was successful in getting data in the coloumns.  

 

Is there a way I extract to data model, only when condition is satified, Example 

I am only keen on data which the status is Enrolled, rest of the data should be ignored for processing so that I don't have to keep adding a condition in every statement and filter, also for data security, I don't want other than data where status is "Enrolled"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors