cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Account6910
New Member

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

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):

FilesNameDateModifiedNew column "Version Control"
0120-Apr-21Current

02

19-Apr-21Current
0115-Apr-21DELETE
0105-Apr-21DELETE
0230-Mar-21ME2021-03
0117-Mar-21ME2021-03
0116-Mar-21Delete
0115-Mar-21Delete
0215-Mar-21Delete

 

 

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

 

 

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

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"

Vera_33_0-1621559050602.png

 

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

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"

Screenshot 2021-05-21 041005.png

Vera_33
Super User
Super User

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"

Vera_33_0-1621559050602.png

 

View solution in original post

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

Thank you.

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors