Monthly Filtering, Excel

[KHammer]

I posted this earlier in the year to no avail...

I am hoping to have a workbook for a set of data collected by Esri`s survey123,

I would like to bracket this workbook into a collection of monhtly worksheets.

Does anyone have any tips for both taking the previous month`s data, putting it into a new worksheet and renaming that worksheet to match the month and year?

Any help would be appreciated.

[BCBuizer]

Hi @KHammer ,

Not exactly sure what your question is, but you can run a scheduled flow every month to collect your data and then save it in a new worksheet in an existing Excel filed using the "Create Worksheet" action in the Excel Online (Business) connector:

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#create-worksheet

In case you need any further help, please be specific in your question(s).

[KHammer]

Hello @BCBuizer ,

I am aware of both the timed flows as well as the create a worksheet option.

What I am not sure is how to both:

A) Name the worksheet for the month and year of the data

B) Only select the previous month`s data to put into said new worksheet.

Ideally I won`t have to delete an master worksheet with all the data, instead I just want new worksheets for one month`s worth of Survey123 results.

[BCBuizer]

Hi @KHammer ,

A) Let's say you run a scheduled flow, you can use the below expression for the Worksheet title to include name and year:

formatDateTime(utcNow(), 'MMM-yyyy')

B) You will need to apply some type of filtering, but the action for that depends on what you use for a data source. Can you please elaborate on the data source you are using?

[KHammer]

@BCBuizer 

B) The original data source is put into the workbook via the Esri Survey 123 connector, so anytime a survey response is recorded those answers are placed into Excel.

That means within the workbook there will be an original worksheet that will act as a placeholder for the survey results. These results will contain the date the answer was submitted so I assume I can filter that way but I have not seen a clean cut way to filter for the previous month`s worth of data.

That being said.. I assume that I need to first create the worksheet, name it, and then get the pevious month`s data from the table of the main worksheet?

[BCBuizer]

Hi @KHammer ,

With this video you can learn how to get information from Excel, and how to manipulate it:

https://www.youtube.com/watch?v=rPaJVdx_k2E

Once done, instead of sending an email, all you need to do is to create the new worksheet and populate that with the information.

[KHammer]

Hello @BCBuizer 

This video was very helpful, thank you.

I assume also instead of a manual trigger I can use this to occur monthly.

This doesn`t however really cover my own personal hurdle (becaue I am not quite sure how to do the filtering) of selecting only items in the past month.. I know now() can be used for the time of the functions activation, but I am not sure how to manipulate that for the previous month...

now() - some function is what I assume but because each month has a different number of days it can`t just be a static number.. 

[BCBuizer · Accepted solution]

Hi @KHammer  , 

I would indeed run a scheduled flow so to not have to do this manually.

Whilst looping through all of the listed rows, you can filter for dates larger than 

addToTime(utcNow(),-1,'Month','yyyy-MM-dd')

to get all records from the last month. 

Those can then be added to the new worksheet.

[KHammer]

@BCBuizer  Thank you I think this should work!

I will let you know if otherwise, thank you so much