locked
Conditional Replace Value RRS feed

  • Question

  • Hi,

    I have a table, Table1, which includes 2 columns: age and custom. I want to replace some values in col custom based on col age.

    If age < 40, Table.ReplaceValue(Table1,"COMPANY","apple",Replacer.ReplaceText,{"custom"}),

    If age > 40 and age < 80, Table.ReplaceValue(Table1,"LIFE","apple",Replacer.ReplaceText,{"custom"}),

    If age > 80, Table.ReplaceValue(Table1,"FIRM","apple",Replacer.ReplaceText,{"custom"})

    Basically, change


    age custom
    10 COMPANY LIFE FIRM
    70 COMPANY LIFE FIRM
    90 COMPANY LIFE FIRM

    to

    age custom
    10 apple LIFE FIRM
    70 COMPANY apple FIRM
    90 COMPANY LIFE apple

    I know this can be done using some technique like adding a new column, remove old "custom", rename new column as "custom". Is there some way to do this within one step?

    Thanks

    Tuesday, May 31, 2016 6:30 PM

Answers

  • Try this:

    = Table.ReplaceValue(PreviousStepName, each if [age] <= 40 then "COMPANY" else if [age] > 40 and [age] <= 80 then "LIFE" else if [age] > 80 then "FIRM" else [custom], each if [age] <= 40 then "apple" else if [age] > 40 and [age] <= 80 then "apple" else if [age] > 80 then "apple" else [custom], Replacer.ReplaceText, {"custom"})

    Ehren

    • Marked as answer by bjzk Monday, June 6, 2016 3:05 PM
    Thursday, June 2, 2016 6:16 PM

All replies

  • Try this:

    = Table.ReplaceValue(PreviousStepName, each if [age] <= 40 then "COMPANY" else if [age] > 40 and [age] <= 80 then "LIFE" else if [age] > 80 then "FIRM" else [custom], each if [age] <= 40 then "apple" else if [age] > 40 and [age] <= 80 then "apple" else if [age] > 80 then "apple" else [custom], Replacer.ReplaceText, {"custom"})

    Ehren

    • Marked as answer by bjzk Monday, June 6, 2016 3:05 PM
    Thursday, June 2, 2016 6:16 PM
  • Thanks. It works.
    Monday, June 6, 2016 3:05 PM
  • Hi Ehren,

    One question. Let's call your method as method 1. We call "adding a new column, remove old "custom", rename new column as 'custom'" as method 2. I just made some experiments. Method 1, even though takes fewer steps, takes more time to refresh. Method 2, takes more steps, however, takes less time to refresh.

    In order to make the test accurate, I use below VBA code, which can hold the refreshment of a query.

    For Each cn In ThisWorkbook.Connections
    If cn = "Query - Method1" Then
    With cn
    .OLEDBConnection.BackgroundQuery = False
    .Refresh
    .OLEDBConnection.BackgroundQuery = True
    End With
    End If
    Next cn

    Could you please explain this? 

    Thanks


    • Edited by bjzk Monday, June 6, 2016 3:44 PM
    Monday, June 6, 2016 3:43 PM
  • It's probably because my approach is doing the "if" checks twice, once for the search, and once for the replacement.

    Since your above example is just replacing everything with "apple", you could possibly speed things up by doing this:

    = Table.ReplaceValue(PreviousStepName, each if [age] <= 40 then "COMPANY" else if [age] > 40 and [age] <= 80 then "LIFE" else if [age] > 80 then "FIRM" else [custom], "apple", Replacer.ReplaceText, {"custom"})

    If this isn't the case for your real-world scenario, then consider going with the custom column approach.

    Ehren


    Monday, June 6, 2016 5:53 PM
  • I do have a if something "apple", else "orange" scenario. I will stick with the custom column approach then. Thanks for the explanation.
    Monday, June 6, 2016 7:11 PM
  • Don't know if the following technique is any faster for replacing the value - it uses a general purpose function that I call Logical_Switch (couldn't think of a better category to put it under). It works like the SWITCH function in Excel 2016 or DAX.

    (valueToFind as any, listOfLists as list, elseCondition as any) =>
    let
       filter = List.Select(listOfLists, each _{0} = valueToFind),
    in
       if not List.IsEmpty(filter) then List.First(filter){1} else elseCondition 

    To use in the current scenario, The function would be applied as follows:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"age", Int64.Type}, {"custom", type text}}),
        ReplacedValue = Table.ReplaceValue(
                            ChangedType, 
                            each  Logical_Switch(
                                      true, 
                                      {
                                        {[age] <= 40, "COMPANY"}, 
                                        {[age] > 40 and [age] <= 80, "LIFE"},
                                        {[age] > 80, "FIRM"}
                                      },
                                      {"custom"}
                                  ), 
                            each Logical_Switch(
                                     true, 
                                     {
                                       {[age] <= 40, "apple"}, 
                                       {[age] > 40 and [age] <= 80, "apple"},
                                       {[age] > 80, "apple"}
                                     },
                                     {"custom"}
                                 ),
                           Replacer.ReplaceText, 
                           {"custom"}
                        )
    in
        ReplacedValue

    Monday, June 6, 2016 11:07 PM
  • Colin, this is really smart!

    As you say it's general purpose and I would like to use it dynamically, so that the conditions are not hardcoded in but sit in a table that the user could adjust. I've used Expression.Evaluate for it so far, but yours would be a lighter alternative that I'd prefer.

    But there's one obstacle where I need your help: How to deal with the " " that are going to be wrapped around the condition string, once I import it from a table?

       { {"[age] <= 40", "COMPANY"}, {"[age] > 40 and [age] <= 80", "LIFE"}, {"[age] > 80", "FIRM"} }

    Any idea how to solve this? Thank you!


    Imke Feldmann TheBIccountant.com

    Tuesday, June 7, 2016 5:54 AM
  • For conditional transformations of values in one or several columns based on the values of one or several columns without adding a calculated column, the general-purpose pattern below can also come in handy:

    = Table.FromRecords(Table.TransformRows( InitialTable, 
    (row) as record => 
      if row[Col1]="This" then 
        Record.TransformFields(row,{{"Col to transform1", each "New value"},
    {"Col to transform2", each "yet another value"}}) else row))
    I have not tested if it is any faster than the more readable method of adding a new column, remove old "custom", rename new column as "custom", especially when used on a single column. The pattern above allows to transform several columns in one step.

    Tuesday, June 7, 2016 5:13 PM
  • Hi Imke,

    Good question. I imagine that you would need to evaluate such an expression, if it's possible at all. The solution would be easy if the inequalities were fixed and not user changeable. I'll continue to look for a solution.

    Tuesday, June 7, 2016 6:02 PM
  • Hi Bertrand,

    this looks very interesting, but I couldn't manage to make it work. How would I integrate it into the mentioned solution?


    Imke Feldmann TheBIccountant.com

    Tuesday, June 7, 2016 6:24 PM
  • Hi Colin,

    thank you - yes, evaluating works (totally forgotten that I did sth like it on Ken Puls' Banding function). And it's OK, because we don't need #shared as an environment. But always think that this a bit like cheating :-)

    So I thought that there might be a special data type / grammar that didn't get so far.

    So don't spend too much time on it if this isn't obvious to you.

    Thanks again!


    Imke Feldmann TheBIccountant.com

    Tuesday, June 7, 2016 6:30 PM
  • In my expample, 'Col1', 'Col to transform1', 'Col to transform2' are names of columns in InitialTable.

    I guess that something like this could work, but I have not tested it

    = Table.FromRecords(Table.TransformRows( InitialTable, 
    (row) as record => 
      let 
         age=row[age],
         band = {
                  {(x)=>x<40,0},
                  {(x)=>x<80,1},
                  {(x)=>true,2}
                 },
         bandresult=List.First(List.Select(band,each _{0}(age))){1},
       replacevalue={"ValueA","ValueB",ValueC"}{bandresult},
       result= Record.TransformFields(row,{{"Custom", each replacevalue}})
    in
       result
    
    ))

    Tuesday, June 7, 2016 9:06 PM
  • Thank you! Didn't recognize Record.TransformFields before - this will probably proof very useful in the future.

    In this case, it seems to replace the complete content of the field "CUSTOM" - so not exactly what we need here. Or didn't I implement it correctly?


    Imke Feldmann TheBIccountant.com

    Wednesday, June 8, 2016 5:42 AM
  • Imke,

    Here is the complete code using the Table.FromRecords > Table.TransformRows > Record.TransformFields pattern:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"age", Int64.Type}, {"custom", type text}}),
        transform = Table.FromRecords(Table.TransformRows( #"Changed Type", 
    	   (row) as record => 
    		let 
    			 age=row[age],
    			 band = {
    				  {(x)=> x<40,"COMPANY"},
    				  {(x)=> x<80,"LIFE"},
    				  {(x)=> true,"FIRM"}
    				 },
    			 replacevalue=List.First(List.Select(band,each _{0}(age))){1},
    			 result= Record.TransformFields(row,{{"custom", each Text.Replace(_,replacevalue,"apple")}})
    		in
    		   result
    		))
    in
        transform

    Wednesday, June 8, 2016 7:52 AM
  • Thanks Bertrand! Now working.

    Imke Feldmann TheBIccountant.com

    Wednesday, June 8, 2016 5:24 PM
  • Hi All, 

    Would it also be possible when using the code of Bertrand to use the column value in stead of C or S as inserted below. When I now replace C for example with [Test] then I get an error. What I would like to do get the last 5 or 6 charachters by using Text.End([Test],5) and add a prefix to it. But I seem to be not allowed to use the column itself in the replacement?

    Regards, 

    Michiel 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}}),
        #"Added Custom" = Table.FromRecords(Table.TransformRows(#"Changed Type", 
    (row) as record => 
      if Text.Length(row[Test]) = 5 then 
        Record.TransformFields(row,{{"Test", each "C"}}) 
        else if Text.Length(row[Test]) = 6 then 
        Record.TransformFields(row,{{"Test", each "S"}})
      else 
        row))
    in
        #"Added Custom"

    Thursday, March 7, 2019 8:51 AM
  • Hi all,

    I see the question is answered but I want to share another kind of solution.

    It's quite similar to an Excel's approximate Index/Match combo. Full code below:

    let
        Source = Excel.CurrentWorkbook(){[Name="Test"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"age", Int64.Type}, {"custom", type text}}),
        Bands = {0,40,80},
        OldValues = {"COMPANY", "LIFE", "FIRM"},
        NewValues = {"apple", "banana", "orange"},
        FnMatch = (fnList, fnValue) => List.PositionOf(fnList, fnValue, Occurrence.Last, (x,y) => x < y),
        Replaced = Table.ReplaceValue(
            ChangedType,
            each OldValues{FnMatch(Bands, [age])},    
            each NewValues{FnMatch(Bands, [age])},
            Replacer.ReplaceText,
            {"custom"})    
    in
        Replaced

    Hope you like it.

    Daniel

    Thursday, March 7, 2019 4:20 PM
  • Hi,
    take this a shot,you can do suchlike replace in one syntax.
    = Table.ReplaceValue(Source,each Number.ToText(List.PositionOf({40,80},[age],0,(a,b)=>a>=b),"LIFE;FIRM;COMPANY"),"apple",(x,y,z)=>Text.Replace(x,y,z),{"custom"})

    Monday, March 11, 2019 2:58 AM