Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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)

7 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        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