Answered

How to split values in a column using Power Automate to create a flow?

(0) Share

Report

Posted on by Community Power Pla...

Hi guys,

I have an example table with a column called "Test". Within "Test" column there are characters that I would like to split and only return specific values back and put it into a new column in Excel spreadsheet.

Name	Price	Test
John Doe	$3,00
Jane Doe	$10,00	X=12345;Y=67890
Peter Johnson	$100,00	X=12345

As you can see the table above, the first column has an empty string in the column "Test". I would like to only return the value of X and Y but remove the rest such as semicolon, X= and Y=. How can I achieve this flow using Power Automate? Can someone please do an example of this scenario in a flow as I got a failed for the first row due to there's an empty string in the column "Test". How can we skipped that and move onto the next row to split and return on the values of number and put it into two new column called X and Y?

Please help and advise.

Categories:

Building flows

I have the same question (0)

All responses (28)

Answers (2)

Paulie78 8,422 Moderator on at

Like (0)

Report
Because this requires a bit of string manipulation, is the table above an accurate example of the HTML code that your table contains, the above is:

<table border="1" width="100%"><tbody><tr><td width="33.333333333333336%" height="30px">Name</td><td width="33.333333333333336%" height="30px">Price</td><td width="33.333333333333336%" height="30px">Test</td></tr><tr><td width="33.333333333333336%" height="30px">John Doe</td><td width="33.333333333333336%" height="30px">$3,00</td><td width="33.333333333333336%" height="30px"> </td></tr><tr><td width="33.333333333333336%" height="30px">Jane Doe</td><td width="33.333333333333336%" height="30px">$10,00</td><td width="33.333333333333336%" height="30px">X=12345;Y=67890</td></tr><tr><td width="33.333333333333336%" height="30px">Peter Johnson</td><td width="33.333333333333336%" height="30px">$100,00</td><td width="33.333333333333336%" height="30px">X=12345</td></tr></tbody></table>

Is that something similar to your table?

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

Like (0)

Report

Hi Paulie,

I guess that's correct with the HTML that you had above. The table is an example from an excel spreadsheet and I would like to know how I could make the flow work using Power Automate? The split values will have to put it back onto the excel spreadsheet with the correct values. I hope this make sense.

Best regards
Aaron

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report
ah ok, I misunderstood that you were looking to grab the contents of a HTML table. You are reading from an Excel table, which is already in a format the Power Automate understands (JSON). You have two choices:

You can put an oData query on your "List Rows Present in a table" action.

You can run a filter the output of your "List Rows Present in a table" action.

Either way, what you are attempting to do is get rid of the rows with blank data in the column Test. Example below:

The query here is simply:

Test ne ''

Column test not equal to nothing. Does that help?

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

Like (0)

Report

Hi Paulie,

Thank you for your quick response. I have tried your steps but the part where I am stuck is after selecting the table. When I tried to enter the Filter Query, it doesn't recognise the column name from the table.

It was showing something like that in the above image. What do you think could cause this issue? I tried another way using get table and the do a compose function to test if any column return back any values and it did. It's just when I select list rows present in a table, it doesn't recognise the column name.

Please advise and help.

Best regards
Aaron

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

Just type the column name in by hand as per the screenshot I sent you. It is case sensitive, so simply:

Test ne ''

Will do what you want.

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

Like (0)

Report

I've tried what you have suggested but when I run the flow it's providing me this error message. Please don't mind the last flow the failure occur in step 2.

I'm not sure what is the problem here, please advise.

Best regards
Aaron

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

Can you show a screenshot of your "list rows present in a table" action please?

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

Like (0)

Report

I hope this helps.

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

Is the column called "Test" hidden on your Excel sheet?

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

Like (0)

Report

yes it is.

Was this reply helpful? Yes No