locked
nested if statement inside Table.Expand RRS feed

  • Question

  • Hi

    Would it be possible to include a "if" statement inside the Table.ExpandTableColumn function? What I want to achieve is

    if [ACTAWARD] = "GENBLK" then [Base Salary]*(1.1756) else [Base Salary]*(1.1891)

    Thank you in advance.

    Wednesday, July 24, 2019 3:16 AM

Answers

  • Hi,

    No, it's not possible. But after expanding you may add such step:

    replace = Table.ReplaceValue(YourLastStep,0,each if [ACTAWARD] = "GENBLK" then 1.1756 else 1.1891,(a,b,c)=>a*c, {"Base Salary"})
    • Edited by Aleksei Zhigulin Wednesday, July 24, 2019 8:25 AM
    • Marked as answer by M.Awal Wednesday, July 24, 2019 10:13 AM
    Wednesday, July 24, 2019 8:24 AM
  • Do you work for Excel Product team?

    No, I don't, but thank you for the compliment :) 

    About coding skills: recently Colin Banfield answered similar question, I couldn't have done better myself.

    About the function's code: recently I posted picture. Hope, it's self-explanatory.

    About the column reference: the last argument of Table.ReplaceValue is the list of columns to search through, i.e. there may be several columns, not only one.

    • Edited by Aleksei Zhigulin Wednesday, July 24, 2019 2:05 PM
    • Marked as answer by M.Awal Wednesday, July 24, 2019 9:22 PM
    Wednesday, July 24, 2019 2:00 PM

All replies

  • Hi,

    No, it's not possible. But after expanding you may add such step:

    replace = Table.ReplaceValue(YourLastStep,0,each if [ACTAWARD] = "GENBLK" then 1.1756 else 1.1891,(a,b,c)=>a*c, {"Base Salary"})
    • Edited by Aleksei Zhigulin Wednesday, July 24, 2019 8:25 AM
    • Marked as answer by M.Awal Wednesday, July 24, 2019 10:13 AM
    Wednesday, July 24, 2019 8:24 AM
  • Hi Aleksei: As always thank you. How did you build up coding skills? Do you work for Excel Product team?

    Can you please explain the following bits in the function? How does it work? My understanding is that ()=> means defining a function, anything inside the parenthesis is optional. Why there are three letters a,b,c? I also get confused when the column reference is made inside a curly bracket, which represents a list. 

    (a,b,c)=>a*c, {"Base Salary"})

    Wednesday, July 24, 2019 10:13 AM
  • Do you work for Excel Product team?

    No, I don't, but thank you for the compliment :) 

    About coding skills: recently Colin Banfield answered similar question, I couldn't have done better myself.

    About the function's code: recently I posted picture. Hope, it's self-explanatory.

    About the column reference: the last argument of Table.ReplaceValue is the list of columns to search through, i.e. there may be several columns, not only one.

    • Edited by Aleksei Zhigulin Wednesday, July 24, 2019 2:05 PM
    • Marked as answer by M.Awal Wednesday, July 24, 2019 9:22 PM
    Wednesday, July 24, 2019 2:00 PM
  • Ta. I never would have thought about it, if I hadn’t seen the explanation
    Wednesday, July 24, 2019 9:22 PM