Filter an excel table and paste the result to a different table

(0) Share

Report

Posted on by spsn

Hi,

I managed to extract the data in terms rows & columns in Power Automate Desktop. My flow looks like as follow:

but from here, I cannot use all the cell range, only partial of it, for example below is the range of cells of data from the input file:

I only want to extract the ones highlighted in yellow and paste the items to a different table.

How to execute this? I don't see any flow that I can directly use to identify a table in the desktop.

Tia.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Henrik_M 2,021 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Ahhh, manipulation of datatable; one of the concepts where Power Automate Desktop falls short.

Here is what I would try:

For each loop down through the datatable.

Each loop item becomes a datarow.

Work your conditional logic on each of these rows, and if it is "yellow", add it the current item to a new list.

After the loop is over, write your list of yellows into your new sheet.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

I don’t know what you are filtering on, let’s assum it is Invoice No.

Create List %NeededRows%
Launch Excel
Get First Free Row
Read from Excel A1 to H and %FirstFreeRow - 1% to %ExcelData%, title row = true
Read from Excel A1 to H1 as %CurrentInvoice% ‘Note: Title row only, we will add the rows we want through loop logic.
For Each %CurrentItem% in %ExcelData%
If %CurrentItem[‘Invoice No’] = G004
Set variable %CurrentInvoice% to %CurrentInvoice + [CurrentItem]% to %CurrentInvoice%
‘Note: this should add the datarow to the datatable %CurrentInvoice%. You might have to play around with the syntax here. Check the Advanced Datatype section of this page if it’s not working: https://docs.microsoft.com/en-us/power-automate/desktop-flows/variable-data-types#advanced-data-types

End (If)
End (For each)

good luck

Was this reply helpful? Yes No
UK_Mike on at

Like
a
(0)

Report
Copy link

Link copied!

Not sure what the filter is nor if its being appended or not, but Power Query within Excel and write the result to new sheet ?

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @spsn

What is your criteria to filter those yellow rows?

Was this reply helpful? Yes No
spsn 33 on at

Like
a
(0)

Report
Copy link

Link copied!

The criteria is Billing period

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Tia,

You can use Excel as a database and then query on it.

ExcelFile is a variable containing the full path of Excel

After executing the above SQL statement I get the desired rows in a datatable called as QueryResult.

Then write this datatable to the new place where you want to write using "Write to Excel Worksheet" action.
I know the date formats are changed but this is just to get you started and you can try working on the date formats.

Was this reply helpful? Yes No
spsn 33 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi VJR,

I never use SQL connection before. Do I do it after I get the Excel data or I do this SQL portion before?

THanks.

Was this reply helpful? Yes No