How to find MAX date for a Month based on File Names with Dates

(0) Share

Report

Posted on by CU06081659-0

122

Hi,

I have to create a Flow based on the below 2 conditions. The file names are always in 'XYZ_ddMMyy.xlsx' format.

Condition 1: If the file is not more than 14 days from today then it should be kept as it is
Condition 2: If the file belongs to the last day of every month or it is the last file of that month whether it is on last day or not then it should be kept as it is

I have been able to identify the 1st condition fully and also the 2nd condition partially i.e. whether the file belongs to the last day of the month or not. The portion which I am not been able to create is if the file doesn't belong to the last day of the month but it was the last file that we had received for that month. E.g. if we may have received a file on 24th and it was the last file that we had received for that month then this too should be considered. I mean I need the file with the MAX date for a month.

I am attaching the screenshots of the Flow and would appreciate if anyone can help me.

1st Compose Action: FilenameWithExtension
2nd Compose Action: substring(outputs('Compose_-_File_Name_with_Extension'), 25, 6)

3rd Compose Action: formatDateTime(concat('20', substring(outputs('Compose_-_Extracted_Date'), 4, 2), '-', substring(outputs('Compose_-_Extracted_Date'), 2, 2), '-', substring(outputs('Compose_-_Extracted_Date'), 0, 2)), 'yyyy-MM-dd')

4th Compose Action: formatDateTime(addDays(startOfMonth(addDays(outputs('Compose_-_Formatted_Date'), 1)), -1), 'yyyy-MM-dd')

Thank you.

Categories:

Building flows

I have the same question (0)

All responses (20)

Answers (1)

Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report
Compose (a list of file names)

[ "XYZ_010124.xlsx", "XYZ_200124.xlsx", "XYZ_210124.xlsx", "XYZ1_290124.xlsx", "XYZ2_290124.xlsx", "XYZ_130124.xlsx", "XYZ_020124.xlsx", "XYZ_010124.xlsx", "XYZ_010224.xlsx", "XYZ_200324.xlsx", "XYZ_200324.xlsx", "XYZ_290324.xlsx", "XYZ_130324.xlsx", "XYZ_310324.xlsx", "XYZ_200324.xlsx", "XYZ_300324.xlsx", "XYZ_240524.xlsx" ]

Select (adds properties for filtering and sorting)

From

outputs('Compose')

Map Name

item()

Map dd

take(last(split(item(), '_')), 2)

Map MMyy

take(skip(last(split(item(), '_')), 2), 4)

Map Date

concat( '20', take(skip(last(split(item(), '_')), 4), 2), '-', take(skip(last(split(item(), '_')), 2), 2), '-', take(last(split(item(), '_')), 2) )

Filter array (filters for less or equal today minus 14 days)

From

body('Select')

Filter

item()['Date']

is less or equal to

addDays(utcNow(), -14, 'yyyy-MM-dd')

Select 2 (finds the last date per month)

From (array of distinct months from property MMyy)

union( xpath( xml(json(concat('{"Root":{"Item":', body('Filter_array'), '}}'))), '//MMyy/text()' ), json('[]') )

Map (finds the biggest dd of the current MMyy)

xpath( xml(json(concat('{"Root":{"Item":', body('Filter_array'), '}}'))), concat('//Item[MMyy ="', item(), '" and not(./dd < ../Item[MMyy ="', item(), '"]/dd)]/Name/text()') )

Compose 2 (flatens the nested arrays from Select 2)

first(json(replace(string(body('Select_2')), '],[', ',')))

Was this reply helpful? Yes No
CU06081659-0 122 on at

Like (0)

Report

Hi @Chriddle ,

Do I add these steps by removing the ones that I have already added as mentioned in my previously attached screenshot? If yes then I tried removing all the steps and tried creating with a fresh one following your steps but getting the attached error. I presume I may have issue with the Compose step. How can I get the list of all Sharepoint files in Compose step. You have specified the files in your Compose step but they need to come from Sharepoint folder.

Apologies for asking such basic questions as I am not a Pro.

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report

I assume that "value" is your list of filenames (but this is just a guess 😉)

If so, simly remove [ and ] from the Compose's input (including the additional lines). "value" is already an array.

Was this reply helpful? Yes No
CU06081659-0 122 on at

Like (0)

Report

Got error in the next step

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report

What is the output of the Compose?

Was this reply helpful? Yes No
CU06081659-0 122 on at

Like (0)

Report

Hi,
I am sharing the screenshots of my entire flow which I have created based on your solution.

Compose – Total Count of Files
length(outputs('Get_files_(properties_only)')?['body/value'])

Compose
‘value’ from “Get files (properties only)”

Select
I have used what you asked me to

Filter array
I have used what you asked me to

Select 2
I have used what you asked me to

Compose 2
I have used what you asked me to

Was this reply helpful? Yes No
Verified answer

Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report
This will give you the list of file names:

Select

From

outputs('Get_files_(properties_only)')?['body/value']

Map

item()['{FilenameWithExtension}']

Compose

body('Select')

Was this reply helpful? Yes No
CU06081659-0 122 on at

Like (0)

Report

Hi @Chriddle ,

It has worked like a charm...Many many thanks for taking time out of your schedule and helping me sort this out. One last query, I am sharing the screenshots. One would show the output of files with MAX dates for each months and in the other I have tried to use the output from the previous i.e. MAX dates against the File Names within a Condition right inside Apply to each wherein I am trying to compare if that particular file in Apply to each matches any name from the output of MAX dates and then do some Actions. I hope I am doing this right?

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (0)

Report
That's how I would build it.

The final "Apply to each" loops over the items from "Get files" that are latest in each month

(The "Compose" there is only a placeholder for the actions that you do with each of these items)

Get_files_(properties_only)

Filenames (Select)

from:

outputs('Get_files_(properties_only)')?['body/value']

select:

item()?['{FilenameWithExtension}']

FilenamesEnriched (Select)

from:

body('Filenames')

map Name:

item()

map dd

take(last(split(item(), '_')), 2)

map MMyy

take(skip(last(split(item(), '_')), 2), 4)

map Date

concat( '20', take(skip(last(split(item(), '_')), 4), 2), '-', take(skip(last(split(item(), '_')), 2), 2), '-', take(last(split(item(), '_')), 2) )

FilenamesFilteredForDate (Filter array)

from:

body('FilenamesEnriched')

where:

lessOrEquals(item()['Date'], addDays(utcNow(), -14, 'yyyy-MM-dd'))

FileamesLastDatePerMonth (Select)

from:

union( xpath( xml(json(concat('{"Root":{"Item":', body('FilenamesFilteredForDate'), '}}'))), '//MMyy/text()' ), json('[]') )

map:

xpath( xml(json(concat('{"Root":{"Item":', body('FilenamesFilteredForDate'), '}}'))), concat('//Item[MMyy ="', item(), '" and not(./dd < ../Item[MMyy ="', item(), '"]/dd)]/Name/text()') )

FilenamesFinal (Compose)

input:

first(json(replace(string(body('FileamesLastDatePerMonth')), '],[', ',')))

FilePropertiesLastFilePerMonth (Filter array)

from:

outputs('Get_files_(properties_only)')?['body/value']

where:

contains(outputs('FilenamesFinal'), item()?['{FilenameWithExtension}'])

Apply_to_each

body('FilePropertiesLastFilePerMonth')

Compose

items('Apply_to_each')

Was this reply helpful? Yes No
CU06081659-0 122 on at

Like (0)

Report

Hi @Chriddle ,

I followed your steps and it is showing me partially correct output. I may not have been able to explain you properly. I need to keep files as following:

1. The files for the last 14 days including today will be there in SharePoint folder irrespective of dates. Nothing needs to be done for these
2. For the rest of the files I would be following MAX Date logics i.e. the ones that you have already defined

So, once the Flow runs, I should ideally get to see files for the last 14 days and files belonging to max dates of each month only. Files prior to 14 days and files belonging to other months but not from max dates need to be deleted. Once deleted, I should get an HTML table via email showing the list of files getting deleted with the Created and Age columns.

I really appreciate you taking time and helping me.

Was this reply helpful? Yes No