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

     

12 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super 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
        Super 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"
  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

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

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