cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rareProfessor
Regular Visitor

Using filtered data with Excel sheets

I've got a Form that adds data to a live Excel spreadsheet, and I want to grab entries from just the past week and isolate them - probably into their own list of some sort.

I have managed to create a new worksheet with the date as the name, but I can't get anything to appear in it.

 

I've been working for a while and I think the logic is clear, but I can't find the way to dump the filtered array into the new worksheet. I'm hoping I'm making an obvious mistake that's easy to fix!

 

Summary of steps shown below:

1. Create destination worksheet with date "<day> of <month>"

2. Get the contents of the table with form submissions

3. Find the date of one week ago

4. Filter the contents to only include entries added since one week ago

5. Put filtered data into newly created worksheet

 

Please and thankyou!

 

Flow1.png

Flow 2.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
efialttes
Super User III
Super User III

Hi!

You need a 'Create table' after 'Create worksheet'

You also need an 'Apply to each' just after 'Filter array', then assign 'List rows present in a table' 'value' output as its input.

Next move 'Add a row into a table' inside your 'Apply to each', then assign to 'Item' the desired value by using dynamic content menu and 'List rows present in a table' outputs

 

You will find a great example on how to fill the 'Add a row into a table' 'Item' input here. The example includes 'Create worksheet' and 'Create table' but does not use 'Apply to each' since a single row is added

https://powerusers.microsoft.com/t5/Building-Flows/Updating-an-excel-sheet-which-is-a-subject-of-my-...

 

Final step (remember this shall be done the last one inorder to avoid Power Automate editor become crazy) replace 'Apply to each' input, add 'Filter array' output instead

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

fchopo
Super User II
Super User II

Hello @rareProfessor 

In Excel, dates are internally stored as integer values, which are equal to the number of days between 12/30/1899 and the date. Therefore, you should do the comparison using integer values. Therefore, to get the past time a week ago and compare it against excel date you should use the following expression:

div(sub(ticks(addDays(utcNow(),-7)),ticks('1899-12-30')),864000000000)

This expression is going to get the number of days between 12/30/1899 and a week before today. Then, you can compare this value with the Excel date column (start time column).

Hope it helps!

Ferran

 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

4 REPLIES 4
efialttes
Super User III
Super User III

Hi!

You need a 'Create table' after 'Create worksheet'

You also need an 'Apply to each' just after 'Filter array', then assign 'List rows present in a table' 'value' output as its input.

Next move 'Add a row into a table' inside your 'Apply to each', then assign to 'Item' the desired value by using dynamic content menu and 'List rows present in a table' outputs

 

You will find a great example on how to fill the 'Add a row into a table' 'Item' input here. The example includes 'Create worksheet' and 'Create table' but does not use 'Apply to each' since a single row is added

https://powerusers.microsoft.com/t5/Building-Flows/Updating-an-excel-sheet-which-is-a-subject-of-my-...

 

Final step (remember this shall be done the last one inorder to avoid Power Automate editor become crazy) replace 'Apply to each' input, add 'Filter array' output instead

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

fchopo
Super User II
Super User II

Hello @rareProfessor 

In Excel, dates are internally stored as integer values, which are equal to the number of days between 12/30/1899 and the date. Therefore, you should do the comparison using integer values. Therefore, to get the past time a week ago and compare it against excel date you should use the following expression:

div(sub(ticks(addDays(utcNow(),-7)),ticks('1899-12-30')),864000000000)

This expression is going to get the number of days between 12/30/1899 and a week before today. Then, you can compare this value with the Excel date column (start time column).

Hope it helps!

Ferran

 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

v-litu-msft
Community Support
Community Support

Hi @rareProfessor,

 

Could @fchopo  or @efialttes ' answer to solve your problem?

If they could, you could go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best Regards,
Community Support Team _ Lin Tu

Thanks @efialttes, You helped me finally make the table properly! The 'Apply to each' is still going crazy though, I follow your instructions to the letter but when I click 'Filter array'->'Item' then it throws in an extra 'Apply to each' with the output 'Body'.

 

I appreciate the input, @fchopo, but I'm not sure how to apply that string of code. Do I just copy & paste it into the 'advanced mode' input box with the same input?

 

Some other comments:

- I followed @efialttes' instructions, but "'Filter array' output" wasn't an option, only 'Body' and 'Item'. Here's the funky result when I use the latter, as well as the options I see.Annotation 2020-07-31 154334.png

 

 

 

- Despite the slightly different username this is still me, we did some funky migration at work and accounts are weird now.

- Sorry for the really late reply; this is not my day job. Heck, my day job is crazy atm.

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (27,747)