none
Replacer.ReplaceText RRS feed

  • Question

  • https://1drv.ms/x/s!Amc8fiGpDxekhgw4x6qt-mtjeBe2

    Hi : I need some assistance with the Table.ReplaceValue function, where the above code is not producing the desired output. Possibly the problem lies where the Replacer.ReplaceText function sits.

    Please see above the link of the file. Thanks

    Sunday, August 18, 2019 10:39 AM

Answers

  • Hi M.Awal,

    the specification of the desired output is missing in your file (or your pic?)

    The picture you've uploaded doesn't seem to have anything to do with the problem you've described.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by M.Awal Sunday, August 25, 2019 8:51 PM
    Saturday, August 24, 2019 7:28 AM
    Moderator
  • Hi M.Awal,

    Some columns, mentioned in your code, are missing in your data sample, but I guess, you try to do this:

    = Table.ReplaceValue(#"Changed Type", 0, each if Text.Contains([Comments], "Overtime", Comparer.OrdinalIgnoreCase) or Text.Contains([Comments], "OT", Comparer.OrdinalIgnoreCase) then "Overtime" 
                                            else if [Description] = "Consulting/Tech Services Charges" and [Source] = "ADHOC" then "CCTV Installation and Alarm Upgrade Project" else [Product Name],              
                                            (a,b,c)=> c,  {"Product Name"})
    • Marked as answer by M.Awal Tuesday, August 27, 2019 11:12 AM
    Monday, August 26, 2019 4:24 PM

All replies

  • Hi M.Awal,

    the specification of the desired output is missing in your file (or your pic?)

    The picture you've uploaded doesn't seem to have anything to do with the problem you've described.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by M.Awal Sunday, August 25, 2019 8:51 PM
    Saturday, August 24, 2019 7:28 AM
    Moderator
  • Hi Imke: Thanks for your reply. When I posted the screenshot, it was showing the right one. I am not sure why is it showing as per your screen shot.
    Sunday, August 25, 2019 8:50 PM
  • Hi M.Awal,

    Some columns, mentioned in your code, are missing in your data sample, but I guess, you try to do this:

    = Table.ReplaceValue(#"Changed Type", 0, each if Text.Contains([Comments], "Overtime", Comparer.OrdinalIgnoreCase) or Text.Contains([Comments], "OT", Comparer.OrdinalIgnoreCase) then "Overtime" 
                                            else if [Description] = "Consulting/Tech Services Charges" and [Source] = "ADHOC" then "CCTV Installation and Alarm Upgrade Project" else [Product Name],              
                                            (a,b,c)=> c,  {"Product Name"})
    • Marked as answer by M.Awal Tuesday, August 27, 2019 11:12 AM
    Monday, August 26, 2019 4:24 PM
  • Thanks for your reply.

    I was struggling with scenario where if statements are required in two places. one in where the new value goes and where the Replacer.ReplaceValue sits. Riseman helped me out with some examples. Then I realised I don't need another if statement over here (a,b,c)=>

    Can I confirm the following:

    • Yellow is the new value and Green is the old value
    •  Green and Yellow can swap places
    •  Green is not mandatory (which means if I dont input anything the function will still work)

    Tuesday, August 27, 2019 11:11 AM
  • >>Yellow is the new value and Green is the old value

    No, not old or new - just two equivalent parameters. You may use one, another, both or none in your calculation.

     >>Green and Yellow can swap places

    Yes, exactly.

     >>Green is not mandatory (which means if I dont input anything the function will still work)

    No, you need input some value (any value, you wish).

    I hope, this thread will clarify the matter.

    Tuesday, August 27, 2019 11:29 AM
  • Interesting. I didn't know that. Thank again.
    Tuesday, August 27, 2019 11:32 AM