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

        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