Table.ReplaceValue list not replacing null data consistently

[jfbonterra]

This is a bizarre problem. I have a multi-step replace function in my M Code using Table.ReplaceValue. The syntax is correct and replaces values as I expect, with the exception of null data. I have steps to replace the null data in the command but the data doesn't get transformed. I can replace the null data in a separate step but I'm baffled why my command wouldn't address this. Can someone please enlighten me? I don't understand what I'm missing. here's the syntax

Table.ReplaceValue ( #"No. of Emp replace", null,

    each if [Person Source] is null

    and [LeadSource] = "Advertising"

    and List.Contains ( { "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"}

    )

[Syndicate_Admin]

Hello, could you filter it in the first step and the code would work, if it does not work you could share a screenshot of the code and the result?

[jfbonterra]

Here is the null data I'm seeing after applying the step (highlighted column and the end of the suntax which is also above in full).

[AlexisOlson]

Are you sure the values are actually null rather than the empty string "" or a space? These are really easy to mix up with text.

If this isn't the issue, can you give a examples where this doesn't work as expected (and what you expect instead)?

[jfbonterra]

Yes, the data is null but good call out. Below is a screen shot of the column where nulls don't get transformed after applying the step. They should get converted to the text value "none" but remain as null. Full syntax is above in the original post. 

[AlexisOlson]

I can't reproduce this behavior. What's the simplest case where you can?

Here's what I tried:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZyySxKTS5RCE9NAnMxxGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person Source" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Person Source"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value", null, each if [Person Source] = null then "none" else [Person Source], Replacer.ReplaceValue, {"Person Source"})
in
    #"Replaced Value1"

[jfbonterra]

So you eseentially transformed the data from "" to null and then null to conditionally "none"? 

Here is pared down syntax where still getting the null data:

Table.ReplaceValue(#"Rename Columns",

    null,

each if [Person Source] is null

    and [Lead_Source_Subcategory__c] = "Capterra"

    then "Review Sites"

    else if [Person Source] is null

    and [LeadSource] = "Web Referral"

    then "Web Referral"

    else if [Person Source] = null then "none"

    else [Person Source],

    Replacer.ReplaceValue,{"Person Source"} )

Possibly unrelated but I've also noticed my data type gets converted from text to untyped after applying this replace text step. Not sure why.

[AlexisOlson]

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?

[jfbonterra]

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.

[AlexisOlson]

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.

[jfbonterra]

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"

[AlexisOlson · Accepted solution]

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")

[jfbonterra]

That's awesome! Thank you so much for taking all the time to help me out with this. I really appreciate it!