Version control of files when using "from folder" as data source

[Account6910]

Hi,

my employer is running 30 projects and each project has a forecast held in Google sheets (yack).  Each google sheet has common tab names and common headers in each tab.

I am taking backups of the 30 project files onto our server multiple times per month, then using PowerQuery to edit and format the data, retaining the "FileName" and "DateModified".

Currently, I add the date of the latest download as a parameter and filter all other files, but I now want to monitor the progression of the forecasts by comparing from month to month.

I need help to create a "VersionControl" table so that I can identify: 

A. The most recent version of each project file and label as "Current"

B. Retain the latest version of each project file for previous months eg labelling as "ME2021-02" , "ME2021-01"

C. Identify superfluous iterations of each file to delete

Desired output (currently I have columns 1 and 2 but  want to add column 3):

FilesName	DateModified	New column "Version Control"
01	20-Apr-21	Current
02	19-Apr-21	Current
01	15-Apr-21	DELETE
01	05-Apr-21	DELETE
02	30-Mar-21	ME2021-03
01	17-Mar-21	ME2021-03
01	16-Mar-21	Delete
01	15-Mar-21	Delete
02	15-Mar-21	Delete

I think I can solve it by splitting the table into a table for each project but want to avoid creating 30 separate tables.

Many thanks in advance

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy0HUsKNI1MlSK1YlWMgKKGFoii4DUGJqiixiYousyNtD1TUTVZY4hYoYhYoosYoQhYoCpyABNVSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FilesName = _t, DateModified = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FilesName", Int64.Type}, {"DateModified", type date}}),
    #"Grouped by FileName" = Table.Group(#"Changed Type", "FilesName", {"ar", each Table.Sort(Table.RemoveColumns(_, "FilesName"), {"DateModified", Order.Descending})}),
    Custom1 = Table.TransformColumns(
        #"Grouped by FileName",
        {
            "ar", 
            each let 
                    l = _[DateModified], yyMM = Date.ToText(Date.AddMonths(l{0},-1),"yyMM"), 
                    m = List.Max(List.Select(l, each Date.ToText(_, "yyMM")=yyMM)), 
                    pos=List.PositionOf(l,m,Occurrence.All), 
                    res = {"Current"} & List.Accumulate({1..List.Count(l)-1}, {}, (s,c) => if List.Contains(pos,c) then s&{"ME" & Date.ToText(l{c}, "yyyy-MM")} else s&{"Delete"}) 
                in 
                    Table.FromColumns({l,res}, {"DateModified", "Version Control"})
        }
    ),
    #"Expanded ar" = Table.ExpandTableColumn(Custom1, "ar", {"DateModified", "Version Control"}, {"DateModified", "Version Control"})
in
    #"Expanded ar"

[Vera_33 · Accepted solution]

Hi @Account6910 

Here is one way to do in M, paste in Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy0HUsKNI1MlSK1YlWMgKKGFoii4DUGJqiixiYousyNtD1TUTVZY4hYoYhYoosYoQqEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FilesName = _t, DateModified = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FilesName", Int64.Type}, {"DateModified", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MonthNumber", each Date.Month([DateModified])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"FilesName"}, {{"CurrentDate", each List.Max([DateModified]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"FilesName"}, #"Grouped Rows", {"FilesName"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"CurrentDate"}, {"CurrentDate"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Grouped Rows", {"FilesName", "MonthNumber"}, {{"lastVersion", each List.Max([DateModified]), type nullable date}}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Grouped Rows", {"FilesName"}, #"Grouped Rows1", {"FilesName"}, "Grouped Rows1", JoinKind.LeftOuter),
    #"Added Custom1" = Table.AddColumn(#"Merged Queries1", "lastMonth", each List.Max( List.Difference( [Grouped Rows1][lastVersion],{[CurrentDate]}))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Version Control", each if [DateModified] = [CurrentDate] then "Current"
else if [DateModified]=[lastMonth] then "ME"&Text.From( Date.Year([lastMonth]))&"-"&Text.From([MonthNumber])
else "Delete"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"MonthNumber", "CurrentDate", "Grouped Rows1", "lastMonth"})
in
    #"Removed Columns"

[Account6910]

I dont know how you did it, but you did it. 

Thank you.

[Vera_33]

Hi @Account6910 

Just tried to find the current Max date, then Max date for each month, mark the current Max and preivous Max...it can be shorter, just went through in UI...a little bit long