Forum Discussion

brinky's avatar
brinky
Icon for Helper IV rankHelper IV
3 years ago
Solved

power query replace

Could some one assit what I'm doing wrong, I'm trying to combine "Route" with [Route_No] and is reaming blamk whereas when I add [Epiry_date] is works perfectly.

 

Thanks in advance

 

Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    You're missing the parenthesis

     

    Table.ReplaceValue(#"Filtered Rows", each [Route_Description], each if [Route_No] = 125 then "Route" & Text.From([Route_No]) else [Route_Description], Replacer.ReplaceValue,{"Route_Description"})

5 Replies

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi brinky 

     

    [Route_No] appears to be a numeral try Text.From( [Route_No] ) in your replace by statement instead.

    • brinky's avatar
      brinky
      Icon for Helper IV rankHelper IV
      3 years ago

      Still nothing

       

      = Table.ReplaceValue(#"Filtered Rows", each [Route_Description], each if [Route_No] = 125 then "Route" & Text.From[Route_No] else [Route_Description], Replacer.ReplaceValue,{"Route_Description"})

      • m_dekorte's avatar
        m_dekorte
        Icon for Resident Rockstar rankResident Rockstar
        3 years ago

        You're missing the parenthesis

         

        Table.ReplaceValue(#"Filtered Rows", each [Route_Description], each if [Route_No] = 125 then "Route" & Text.From([Route_No]) else [Route_Description], Replacer.ReplaceValue,{"Route_Description"})

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    3 years ago

    I suggest a somewhat different approach, which also takes into account that the data type of Route_No, which is a string of digits, might be text or number (your column header shows it as type any.

     

    If you will always be certain of the data type for Route_No, you can shorten the code a bit:

     

     

    = Table.FromRecords(
            Table.TransformRows(Source,
             (r)=>
                Record.TransformFields(r, 
                    {"Route_Description", each 
                            if Text.From(r[Route_No]) = "125" then "Route" & Text.From(r[Route_No]) else r[Route_Description]})
                ))