cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Niclasthell
Helper I
Helper I

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

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

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)

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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.