Looping through Excel data stoted in datatable facing issue to check for each cell empty value.

[YogeshNikam]

I have an excel sheet containing 30 to 40 rows and 70 to 80 columns and some columns are empty. So I want to store the rows having blank columns in a list using Power Automate Desktop.

[Kaif_Siddique]

Could you share sample data?

[Agnius]

Use Find or replace in data table and search for %''% (empty value) and make it match the entire cell contents. Like this:

This will return a table of indexes for all matched cells. You can then use the row index to reference the rows with empty values and add them to a new data table or a list.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Nived_Nambiar]

Hi @YogeshNikam 

Try this approach:

Assume this is input datatable

Now use Run .Net script action

To know how to load required References, refer this blog:

https://www.linkedin.com/pulse/how-run-net-script-action-empowers-data-table-power-automate-bysani/

Next how to pass input and output variables in script parameters, refer this 

where %InputDataTable% is the table passed as input and %OutputDataTable% is list having rows having blank columns.

.net code :

dt2= dt1.AsEnumerable().Where(Function(r) r.ItemArray().Select(Function(e) e.ToString.Trim).Contains("")).ToList()

See the output :

You will get output in list as desired.

Overall flow

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[YogeshNikam]

Preferred solution. I got the excel rows having empty value but how to save that rows (eg. row index 1, 3, 5) into data table. Can you please elaborate on this.

Regards,

Yogesh Nikam

[Agnius]

You need to use Create new data table to create a new data table. Then use a For each loop to iterate through the %DataTableMatches% variable. This will result in a %CurrentItem% being created, which will contain the row and column indexes for a single matched value. So, for instance %CurrentItem['Row']% will contain the row index. You can use this to reference a row in the %ExcelData% table.

The problem here is that for some reason PAD returns those indexes as strings and they cannot be used to reference a cell. They must first be converted to numeric values. So, use Convert text to number on %CurrentItem['Row']% to convert it to a valid numeric index. Let's say you'd store it in %RowIndex%.

You can then finally get a row using this index. You can get it as %ExcelData[RowIndex]%. You can use it in the Insert row into data table action to insert the row to the new table you created. 

So, essentially, more or less like this:

1. Create new data table
2. For each %CurrentItem% in %DataTableMatches%:
   1. Convert %CurrentItem['Row']% to number and store it into %RowIndex%
   2. Insert row %ExcelData[RowIndex]% to the new data table
3. End loop

Hope this makes sense.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[YogeshNikam]

Preferred solution. Thank you so much for sharing the knowledge you have.

Regards,

Yogesh Nikam

[Agnius · Accepted solution]

If I have resolved your issue, please press the "Accept as Solution" button on my reply that answered the question.

Thanks.