cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Problem with nested if and else with text.contains

Hi everyone,

 

I´ve a problem with my code in Power Query and I cant get my head around this. 

 

My code below isn´t working and I dont know if it´s because of blank cells in the column or if the code is wrong. I guess it because of blank cells because when there is text it seems to work. Should I do this as a calculated column instead?  I dont get any error and the first line seems to work, but 2 and 3 arent correct. 

 

= Table.AddColumn(#"Replaced Value2", "GT", each if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if [MarketingMedium] = "cpc" or [MarketingMedium] = "referral" and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3)

 

Regards,

niclas

1 ACCEPTED SOLUTION

You can use following

= Table.AddColumn(#"Replaced Value2", "GT", each try if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if ([MarketingMedium] = "cpc" or [MarketingMedium] = "referral") and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3 otherwise 3)

View solution in original post

7 REPLIES 7
Vijay_A_Verma
Super User
Super User

Use this

= if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if ([MarketingMedium] = "cpc" or [MarketingMedium] = "referral") and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi5IVtJRSs/PT89JBTISk5KVYnVgwkmZeelAqjIxIz8fSRiuOqkoMS8Fm0R5URGmMSnpQAgWLkpNSy0qSszBsCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MarketingMedium = _t, MarketingSource = _t, MarketingCampaign = _t]),
    #"Replaced Value2" = Source,
    Custom1 = Table.AddColumn(#"Replaced Value2", "GT", each if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if ([MarketingMedium] = "cpc" or [MarketingMedium] = "referral") and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3)
in
    Custom1
Anonymous
Not applicable

Hi, I opened a new blank query and added the text above, and it creates this table. How is this affecting the other table? Thanks

Niclasthell_0-1655904560845.png

 

This was for illustration.

Create a copy of your original query so that your original code gets saved as a backup.

Now, in your original code, delete everything starting #"Replaced Value2" and paste the below code.

#"Replaced Value2" = Source,
    Custom1 = Table.AddColumn(#"Replaced Value2", "GT", each if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if ([MarketingMedium] = "cpc" or [MarketingMedium] = "referral") and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3)
in
    Custom1
Anonymous
Not applicable

Thanks for your respond! I dont know if I´m doing something wrong. I went back to my orginial table were my previos code are:

 

= Table.AddColumn(#"Replaced Value2", "GT", each if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if [MarketingMedium] = "cpc" or [MarketingMedium] = "referral" and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3)

 

Then I update this with your code. Should It look exactly as below?  

 

#"Replaced Value2" = Source,
    Custom1 = Table.AddColumn(#"Replaced Value2", "GT", each if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if ([MarketingMedium] = "cpc" or [MarketingMedium] = "referral") and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3)
in
    Custom1

 

Use this in place of your previous code i.e. replace

= Table.AddColumn(#"Replaced Value2", "GT", each if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if [MarketingMedium] = "cpc" or [MarketingMedium] = "referral" and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3)

 

WITH

= Table.AddColumn(#"Replaced Value2", "GT", each if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if ([MarketingMedium] = "cpc" or [MarketingMedium] = "referral") and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3)

 

You can use following

= Table.AddColumn(#"Replaced Value2", "GT", each try if [MarketingMedium] = "cpc" and [MarketingSource]= "google" and not Text.Contains([MarketingCampaign], "brand") then 1 else if ([MarketingMedium] = "cpc" or [MarketingMedium] = "referral") and [MarketingSource]= "bing" and Text.Contains([MarketingCampaign], "yahoo") then 2 else 3 otherwise 3)
Anonymous
Not applicable

Thanks! I get the message "[Expression.Error] We cannot convert the value null to type Logical" and some blank cells. Is it possible to write a code to tackle this problem, or should I transform the columns and replace null?

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors