none
How to declare a variable in Custom column formula? RRS feed

  • Question

  • I'm adding a new column and I have a formula like it:

    if Text.Length(Text.Replace(Text.Replace([Text], "-", ""), " ", "") ) = 4
    then "CB0" & Text.Range(Text.Replace(Text.Replace([Text], "-", ""), " ", "") , 2)
    else Text.Replace(Text.Replace([Text], "-", ""), " ", "")

    i want to do something like it:

    string = Text.Replace(Text.Replace([Text], "-", ""), " ", "") ) 

    if Text.Length(string) = 4

    then "CB0" & Text.Range(string, 2)

    else string

    Monday, October 20, 2014 12:46 PM

Answers


  • The issue you have is that you haven't output 'string' anywhere.  You can create a column with the manipulated text and then reference that column when applying the if statement.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    String = Table.AddColumn(Source, "ReplacedText", each Text.Replace(Text.Replace([Text], "-", ""), " ", "")),


    AddColumn = Table.AddColumn(String, "EndResult", each if Text.Length([ReplacedText]) = 4

    then "CB0" & Text.Range([ReplacedText], 2)

    else [ReplacedText])

    in
        AddColumn

    Monday, October 20, 2014 2:12 PM

All replies


  • The issue you have is that you haven't output 'string' anywhere.  You can create a column with the manipulated text and then reference that column when applying the if statement.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    String = Table.AddColumn(Source, "ReplacedText", each Text.Replace(Text.Replace([Text], "-", ""), " ", "")),


    AddColumn = Table.AddColumn(String, "EndResult", each if Text.Length([ReplacedText]) = 4

    then "CB0" & Text.Range([ReplacedText], 2)

    else [ReplacedText])

    in
        AddColumn

    Monday, October 20, 2014 2:12 PM
  • Alternatively, you can define a new function and then apply it:

    let
        Source = Table.FromColumns({{1, 2, 3, 4, 5, 6}, {"a t", "-s-t3", "1 3 2 4", "foo", "bar", "test"}}),
        myFunction = (value) =>
            let
                string = Text.Replace(Text.Replace(value, "-", ""), " ", "")
            in
                if Text.Length(string) = 4 then "CB0" & Text.Range(string, 2) else string,
        Added = Table.AddColumn(Source, "Column3", each myFunction([Column2]))
    in
        Added

    Monday, October 20, 2014 2:20 PM