Answered

How to Replace Values JSON

(0) Share

Report

Posted on by Community Power Pla...

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:

Categories:

Building flows

I have the same question (0)

All responses (16)

Answers (1)

Verified answer

MarvinBangert 1,924 Most Valuable Professional on at

Like (0)

Report
Hey @Anonymous
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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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"
  },

Was this reply helpful? Yes No
MarvinBangert 1,924 Most Valuable Professional on at

Like (0)

Report
Hey @Anonymous
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.

Was this reply helpful? Yes No
eliotcole 4,363 Moderator on at

Like (0)

Report

You can also use the setProperty() function in an expression, @Anonymous . 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)

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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:

Was this reply helpful? Yes No
MarvinBangert 1,924 Most Valuable Professional on at

Like (1)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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.

Was this reply helpful? Yes No
MarvinBangert 1,924 Most Valuable Professional on at

Like (0)

Report

Hey @Anonymous
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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi Marvin,

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

Was this reply helpful? Yes No
MarvinBangert 1,924 Most Valuable Professional on at

Like (0)

Report

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

Was this reply helpful? Yes No