Forum Discussion

jfbonterra's avatar
jfbonterra
Frequent Visitor
3 years ago
Solved

Table.ReplaceValue list not replacing null data consistently

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 s...
Need Help
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

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

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago

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's avatar
    jfbonterra
    Frequent Visitor
    3 years ago

    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's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      3 years ago

      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's avatar
        jfbonterra
        Frequent Visitor
        3 years ago

        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.