March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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)
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
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
53 | |
26 | |
17 | |
13 |