Forum Discussion
Table.ReplaceValue list not replacing null data consistently
In this example, the nulls are not empty strings or actual blanks, they are text "null". However, replacing "null" with null did recreate your issue. Here's a simplified version of what you provided:
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45WckwpSy0qySzOzEtX0lEqTi5IAVJ5pTk5SrE60UoBiUUlealF+s75ecWlOSWJeSUK7qlAgcSSVJC6MBMF54zU5GyFoNTkzILM1LwSZM14jI4FAA==", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LeadSource = _t, Lead_Source_Detail__c = _t, #"Person Source" = _t] ), #"Replaced Value" = Table.ReplaceValue( Source, "null", null, Replacer.ReplaceValue, Table.ColumnNames(Source) ), #"Person Source Replace null" = Table.ReplaceValue( #"Replaced Value", null, each if [Person Source] = null and List.Contains({"Advertising"}, [LeadSource]) then "Digital Advertising" else if [Person Source] = null and Text.Contains([Lead_Source_Detail__c] = "conference") then "Event" else if [Person Source] = null then "none" else [Person Source], Replacer.ReplaceValue, {"Person Source"} ) in #"Person Source Replace null"The problem is with
Text.Contains([Lead_Source_Detail__c] = "conference")On its own, this throws the error "Expression.Error: 1 arguments were passed to a function which expects between 2 and 3".
I haven't dug into why just eats the error when included inside else if instead of returning an error for that row, but all that's needed to resolve it is to replace "=" with ",".
Text.Contains([Lead_Source_Detail__c], "conference")
I created a query (using the Enter Data button) that you can copy and paste into the Advanced Editor of a new blank query. It interprets empty cells as empty strings instead of nulls, which is why I did the first replacement before attempting a simplified version of what you're trying to do.
I can't reproduce your problem from what you've shared (my attempt replaced the nulls just fine). Can you share an entire self-contained query like I did that has the same issue?
I see. How do I create a self-contained query? Does it need to be through a .pbix file? The dataset I'm working in is in an online data flow.
The easiest way is to paste data into Enter Data interface in Power BI Desktop.
https://radacad.com/create-a-table-in-power-bi-using-enter-data-and-how-to-edit-it
I think Blank table is similar when creating a dataflow.
Got it. Below is the query and the issue is replicated when I view the data in PB Desktop.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZJtT8IwEMe/yoX4csm6OTD6DhCNCQKTh4jIi9LdpFnTkrYTX/jhvSGoATTR2PQhd9f2rr/+Z7MaY2lyz6hFatRLW+NmUgtqEY2xlh4zGHru0ZHdzJ7ReumkfiKL0ejhGlolOdBVG3SpFC1XXODCmAL6lguFcMk9h57RK2ty6aHzgsLBK4yWXBcwNWWVLozPw5jFUQBRfMEYdWjeUmCKLgCuM1jjY8lYfGYROOSm1Bn30mjacqOdzBCGXKGDLvLss5Lt4sTq0Nn3S7Q7cx585WAYqodxM6VofITDgFuv0YZto12pPNcerpEcFM6Ogzlh4A2cRIwFdD852ksUBdyhkCuJ1Xlb3dLP801JjTBK/pNGJyvFLr59/SSBvRp+CUin74BO/yKUAx4fkunSxJUsENrd0WRPJT9x+ebpg3KhpIANrQYMjZDouYIW/Rdp8UAi9TCuVxni/QxHmczfAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Country = _t, LeadSource = _t, AnnualRevenue = _t, Lead_Type__c = _t, Budget = _t, #"Conversion Event" = _t, #"Engaged Lead Date" = _t, Registered_501_c_3__c = _t, Lead_Record_Type_mapped_to_acc__c = _t, #"Cause Area" = _t, #"Cause Area Parent" = _t, Lead_Source_Detail__c = _t, #"Demo Request Date" = _t, #"Person Source" = _t, Person_Source_Details__c = _t, Number_of_Employees__c = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Name", Int64.Type}, {"Country", type text}, {"LeadSource", type text}, {"AnnualRevenue", Int64.Type}, {"Lead_Type__c", type text}, {"Budget", type text}, {"Conversion Event", type text}, {"Engaged Lead Date", type datetime}, {"Registered_501_c_3__c", type text}, {"Lead_Record_Type_mapped_to_acc__c", type text}, {"Cause Area", type text}, {"Cause Area Parent", type text}, {"Lead_Source_Detail__c", type text}, {"Demo Request Date", type datetime}, {"Person Source", type text}, {"Person_Source_Details__c", type text}, {"Number_of_Employees__c", type text}}),#"Person Source Replace null" = Table.ReplaceValue ( #"Changed Type", null,
each if [Person Source] is null
and List.Contains ( {"Advertising", "Internet Search - Paid" , "Social Media - Paid", "Paid Search", "Paid Social", "3rd Party Advertising", "LinkedIn Lead Gen", "Facebook Lead Ads" }, [LeadSource] )
then "Digital Advertising"
else if [Person Source] is null and [LeadSource] = "Advertising" and [Lead_Source_Detail__c] = "cpc"
then "Digital Advertising"
else if [Person Source] is null
and List.Contains ( { "Website", "Jumpstart Website" }, [LeadSource] )
and [Lead_Source_Detail__c] <> "cpc"
then "Direct Web"
else if [Person Source] is null and [LeadSource] = "Inbound Inquiry"
then "Direct Inbound"
else if [Person Source] is null and [LeadSource] = "Forward to Friend"
then "Email"
else if [Person Source] is null and [Lead_Source_Detail__c] = "email"
then "Email"
else if [Person Source] is null
and List.Contains ( { "Conference" , "Event partner", "Event" }, [LeadSource] )
then "Event"
else if [Person Source] is null
and Text.Contains ([Lead_Source_Detail__c] = "conference")
then "Event"
else if [Person Source] is null
and [LeadSource] = "List Import"
then "List Import"
else if [Person Source] is null
and [Lead_Source_Detail__c] = "Software Advice"
then "Paid Lead Programs"
else if [Person Source] = null then "none"
else [Person Source],
Replacer.ReplaceValue,{"Person Source"}
)
in
#"Person Source Replace null"In this example, the nulls are not empty strings or actual blanks, they are text "null". However, replacing "null" with null did recreate your issue. Here's a simplified version of what you provided:
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "i45WckwpSy0qySzOzEtX0lEqTi5IAVJ5pTk5SrE60UoBiUUlealF+s75ecWlOSWJeSUK7qlAgcSSVJC6MBMF54zU5GyFoNTkzILM1LwSZM14jI4FAA==", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LeadSource = _t, Lead_Source_Detail__c = _t, #"Person Source" = _t] ), #"Replaced Value" = Table.ReplaceValue( Source, "null", null, Replacer.ReplaceValue, Table.ColumnNames(Source) ), #"Person Source Replace null" = Table.ReplaceValue( #"Replaced Value", null, each if [Person Source] = null and List.Contains({"Advertising"}, [LeadSource]) then "Digital Advertising" else if [Person Source] = null and Text.Contains([Lead_Source_Detail__c] = "conference") then "Event" else if [Person Source] = null then "none" else [Person Source], Replacer.ReplaceValue, {"Person Source"} ) in #"Person Source Replace null"The problem is with
Text.Contains([Lead_Source_Detail__c] = "conference")On its own, this throws the error "Expression.Error: 1 arguments were passed to a function which expects between 2 and 3".
I haven't dug into why just eats the error when included inside else if instead of returning an error for that row, but all that's needed to resolve it is to replace "=" with ",".
Text.Contains([Lead_Source_Detail__c], "conference")