Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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"
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |