none
IF Then Else statement to replace a value in a column RRS feed

  • Question

  • Hi there, I am having some issues with getting the formatting correct on a if .. then .. else statement where I want to replace any trailing commas in 2 columns. (Although removing with the source data query, I can't do that here with this application)

    This code currently works:

    #"Replaced Value"   = Table.ReplaceValue(#"Reordered Columns", each [CTC Issues], each Text.Start([CTC Issues],Text.Length([CTC Issues])-1),Replacer.ReplaceValue,{"CTC Issues"}),
       #"Replaced Value1" =  Table.ReplaceValue(#"Replaced Value", each [CFF Issues], each Text.Start([CFF Issues],Text.Length([CFF Issues])-1),Replacer.ReplaceValue,{"CFF Issues"})

    in
        #"Replaced Value1"

    As not every value ends in a comma, this is removing the last character regardless of what it is.

    If I try the following on the 1st column:

     #"Replaced Value"   = each if [CTC Issues] <> null and Text.End(#"Reordered Columns", [CTC Issues],1) = "," then Table.ReplaceValue(#"Reordered Columns",  [CTC Issues],  Text.Start([CTC Issues],Text.Length([CTC Issues])-1),Replacer.ReplaceValue,{"CTC Issues"}) else  [CTC Issues]
    in
        #"Replaced Value"

    I am getting error where I am being prompted
    Enter Parameter 
    _(Optional) 

    and 2 buttons, Invoke & Clear

    If I click Invoke I get:
    An error occurred in the ‘Q_OPS2’ query. Expression.Error: We cannot apply field access to the type Null.
    Details:
        Value=
        Key=CTC Issues

    Not sure what I am doing incorrectly.  Any advice would be greatly appreciated :-)

    Thursday, January 18, 2018 3:40 PM

Answers

  • Replace the two steps with the following one:

    TransformedColumns = Table.TransformColumns(#"Reordered Columns", {{"CTC Issues", each Text.TrimEnd(_, ",")}, {"CFF Issues", each Text.TrimEnd(_, ",")}})

    • Marked as answer by jhimes Thursday, January 18, 2018 7:42 PM
    Thursday, January 18, 2018 7:26 PM

All replies

  • Replace the two steps with the following one:

    TransformedColumns = Table.TransformColumns(#"Reordered Columns", {{"CTC Issues", each Text.TrimEnd(_, ",")}, {"CFF Issues", each Text.TrimEnd(_, ",")}})

    • Marked as answer by jhimes Thursday, January 18, 2018 7:42 PM
    Thursday, January 18, 2018 7:26 PM
  • Hi Colin & thank you, that is much simpler then how I was trying & works like a charm.

    I was not familiar with TrimEnd. Using your example I was able to add a TrimStart to remove any leading spaces.

    Thursday, January 18, 2018 7:42 PM
  • Hi,

    If you have leading spaces, trailing commas, or both, then use Text.Trim(<text>, {",", " "}). The function would also remove trailing spaces or leading commas.

    Thursday, January 18, 2018 8:03 PM