How to Replace Values JSON

[garethaw]

Hi,

Just some background, I have converted a CSV file to Json - I'm under the impression you cannot edit CSV files within power automate.

I now have an array that looks like this...

[

  {

    "Serial Number": "850375895",

    "Black Toner": "Low",

    "Cyan Toner": "41%",

    "Magenta Toner": "7%",

    "Yellow Toner": "39%",

    "Last Reported": "7/29/2021 9:35 AM\r"

  },

  {

    "Serial Number": "951309875",

    "Black Toner": "Low",

    "Cyan Toner": "75%",

    "Magenta Toner": "74%",

    "Yellow Toner": "81%",

    "Last Reported": "11/01/2021 16:34\r"

  },

I want to replace the low values with 5%, please can you help?

Updated flow with solution:

[MarvinBangert · Accepted solution]

Hey @garethaw 

you could use the "Select" action to update the content within your array and create a new array to go on. I just copied your code piece into an variable, parse the JSON (just use the "Generate from sample" and add your code there). The parsing helps me to select the items, within the value I just use the replace() expression to change the text:

replace(item()?['Black Toner'],'Low','5%')

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

[garethaw]

Hi Marvin,

Thanks for your help, is there a way I can replace multiple values, for example if the "Black Toner" is "High" replace with "100%".

[

  {

    "Serial Number": "850375895",

    "Black Toner": "Low",

    "Cyan Toner": "41%",

    "Magenta Toner": "7%",

    "Yellow Toner": "39%",

    "Last Reported": "7/29/2021 9:35 AM\r"

  },

  {

    "Serial Number": "951309875",

    "Black Toner": "High",

    "Cyan Toner": "75%",

    "Magenta Toner": "74%",

    "Yellow Toner": "81%",

    "Last Reported": "11/01/2021 16:34\r"

  },

[MarvinBangert]

Hey @garethaw 

you can just combine multiple replace() expressions:

replace(replace(item()?['Black Toner'],'Low','5%'),'High','100%')

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

[eliotcole]

 You can also use the setProperty() function in an expression, @garethaw . Plus, you will need to put an if() function in there, too.

We really need to see your flow, mate. 😉 

 I'm not at a computer right now, but however you're creating the json I would recommend doing it on that actual step, so you can then just wrap this around it. However it would be ideal to see your flow so that we can better assist.

 Could you post screenshots? Just black  out sensitive data.

 here's a basic setProperty():

setProperty(data, 'Black Toner', '100%')

 Use that with an if statement ... .  hang on I'll just get to a computer.

 in the meantime can you post your flow? (edited in to the original post is best)

[garethaw]

That's worked, appreciate I'm a bit off topic here but how would I now convert the json back to csv?

Here is the original CSV:

[MarvinBangert]

Hey @eliotcole 

doesn't the setProperty only runs on objects, not on arrays? So you would also need an "Apply to each" and a more complex formula to check for the wright input and afterwards adding the object back into an array variable. Also the "Apply to each" takes usually more time to complete.

Yes, there are multiple ways to achieve this, but i guess using the "Select" with a replace is the most time efficient and easiest in this case.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[garethaw]

Posted my flow, the replace query works. To take it 1 step further how difficult would it be to replace any value between 75%-90% to 91%.

For example If Black Toner = 77% replace with 91%

Anyway of doing this without having 15+ replace expressions.

[MarvinBangert]

Hey @garethaw 

there is an action called "Create CSV table" within the "Data operation" actions. The "From" is your "Select" Output. Within the "Show advanced options" you can choose if you want to create the columns automatically or manually.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[garethaw]

Hi Marvin,

That seems to work okay but the resulting csv has quotations? 

[MarvinBangert]

Hey @garethaw 

can you please show the flow with the select, create CSV table and create file action?

[garethaw]

[eliotcole]

Oh, I absolutely agree, @MarvinBangert . That's why I said:

however you're creating the json I would recommend doing it on that actual step,

That way you can incorporate the 5%/100% conversion and the setting of that in to the same step.

@MarvinBangert wrote:

Hey @eliotcole 

doesn't the setProperty only runs on objects, not on arrays? So you would also need an "Apply to each" and a more complex formula to check for the wright input and afterwards adding the object back into an array variable. Also the "Apply to each" takes usually more time to complete.

Yes, there are multiple ways to achieve this, but i guess using the "Select" with a replace is the most time efficient and easiest in this case.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

---

[MarvinBangert]

Hey @garethaw 

can you also show me the result within the "Create CSV table" when it was executed? Mine looks like this:

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[garethaw]

Hi Marvin, my apologies it looks the the original json has double quotes. Any idea what I've doing wrong?

https://imgur.com/a/iBJmrki

 

[MarvinBangert]

Hi @garethaw 

I'm not really sure where this comes from, it could be something like a formatting issue with your file. Please open the in Notepad Editor and check in the right lower corner what format the file is, here is mine:

With this format it works fine on my side. 

Also I noticed some "\r" at the end of each date, this is the break for the next line. Your flow looks pretty much the same as this: Power Automate: How to parse a CSV File to create a JSON array (tachytelic.net)

To get rid of the "\r" at the end of the date, you need to use this: decodeUriComponent('%0D%0A')

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

[garethaw]

Thanks Marvin!