cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vozniak
Helper I
Helper I

How to Freeze submissions after a specific date

I have a query that pulls data and then uses only the most recent submission in the report. I need to freeze the data from previous periods so they can't be retroactively changed.

 

For example:

Business Unit A submits data for the week ending 1/5 on 1/6 at 8am and 1/7 at 7 and 10:40. When the refresh runs at 1pm, only the 1/7-10:40 data submission is used in the report. Unfortunately, if the Business Unit then resubmits data for the week ending 1/5 on 1/10 (after the report is published) the report will pick that up as the Most Recent submission and use that data. I need to be able to freeze the data so that after the refresh there is no more data accepted for that week ending date.

 

The code for the query (after the file information) is not pretty but it works:

 

#"Renamed Columns1" = Table.RenameColumns(Table1_Table,{{"Reporting Units?", "Reporting Units"}, {"Emergent Work?", "Emergent Work"}, {"Variance Commentary Required?", "Variance Commentary Required"}}),
#"Renamed Columns" = Table.RenameColumns(#"Renamed Columns1",{{"Capital or O&M?", "Capital or O&M"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start time", type datetime}, {"Completion time", type datetime}, {"Email", type text}, {"Name", type text}, {"Week Ending Date", type date}, {"Business Unit", type text}, {"Update Type", type text}, {"Capital or O&M", type text}, {"Capital Functional Area", type text}, {"O&M Functional Area", type any}, {"Reporting Units", type text}, {"Units Planned", Int64.Type}, {"Units Completed", Int64.Type}, {"Planned Value", Int64.Type}, {"Earned Value", Int64.Type}, {"Actual Cost", Int64.Type}, {"Emergent Work", type text}, {"Emergent Work Planned Value", type any}, {"Emergent Work Earned Value", type any}, {"Variance Commentary Required", type text}, {"Variance Commentary", type text}, {"__PowerAppsId__", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Completion time", Order.Ascending}}),
#"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Completion time] <> null)),
Custom2 = #"Filtered Rows1",
#"Sorted Rows1" = Table.Sort(Custom2,{{"Completion time", Order.Descending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows1", {"Business Unit", "Capital or O&M", "Update Type", "Capital Functional Area", "O&M Functional Area", "Week Ending Date"},
{{"Count", each Table.RowCount(_), type number},
{"MostRecent", each List.Max([Completion time]), type datetime},
{"TableData", each _, type table},
{"EV", each List.First([Earned Value]), type number},
{"PV", each List.First([Planned Value]), type number},
{"CompletedBy", each List.First([Name]), type text},
{"Reporting Units", each List.First([Reporting Units]), type text},
{"UnitsPlanned", each List.First([Units Planned]), type number},
{"UnitsCompleted", each List.First([Units Completed]), type number},
{"AC", each List.First([Actual Cost]), type number},
{"EEV", each List.First([Emergent Work Earned Value]), type number},
{"EPV", each List.First([Emergent Work Planned Value]), type number},
{"NeedCommentary", each List.First([Variance Commentary Required]), type text},
{"VarianceCommentary", each List.First([Variance Commentary]), type text}}),
#"Expanded TableData" = Table.ExpandTableColumn(#"Grouped Rows", "TableData", {"Start time", "Completion time", "Email", "Name", "Week Ending Date", "Business Unit", "Update Type", "Capital or O&M", "Capital Functional Area", "O&M Functional Area", "Reporting Units", "Units Planned", "Units Completed", "Planned Value", "Earned Value", "Actual Cost", "Emergent Work", "Emergent Work Planned Value", "Emergent Work Earned Value", "Variance Commentary Required", "Variance Commentary", "__PowerAppsId__"}, {"Start time", "Completion time", "Email", "Name", "Week Ending Date.1", "Business Unit.1", "Update Type.1", "Capital or O&M.1", "Capital Functional Area.1", "O&M Functional Area.1", "Reporting Units.1", "Units Planned", "Units Completed", "Planned Value", "Earned Value", "Actual Cost", "Emergent Work", "Emergent Work Planned Value", "Emergent Work Earned Value", "Variance Commentary Required", "Variance Commentary", "__PowerAppsId__"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded TableData",{"Week Ending Date.1", "Business Unit.1", "Update Type.1", "Capital or O&M.1", "Capital Functional Area.1", "O&M Functional Area.1", "Reporting Units.1", "Units Planned", "Units Completed", "Planned Value", "Earned Value", "Actual Cost", "Emergent Work", "Emergent Work Planned Value", "Emergent Work Earned Value", "Variance Commentary Required", "Variance Commentary"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Business Unit", "Capital Functional Area", "O&M Functional Area", "Capital or O&M", "Update Type", "Week Ending Date", "Count", "MostRecent", "Start time", "Completion time", "Email", "Name", "__PowerAppsId__", "EV", "PV", "CompletedBy", "Reporting Units", "UnitsPlanned", "UnitsCompleted", "AC", "EEV", "EPV", "NeedCommentary", "VarianceCommentary"}),
#"Inserted Merged Column" = Table.AddColumn(#"Reordered Columns", "Combination", each Text.Combine({[Business Unit], [Capital Functional Area], Text.From([#"O&M Functional Area"], "en-US"), [#"Capital or O&M"]}, " "), type text),
#"Added Custom" = Table.AddColumn(#"Inserted Merged Column", "LatestEntry", each [Completion time]=[MostRecent]),
#"Filtered Rows2" = Table.SelectRows(#"Added Custom", each ([LatestEntry] = true))

 

Any help in freezing the data would be appreciated.

 

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @vozniak 

Is my understanding below correct?

business unit A resubmits data for the week ending 1/5 on 1/10, after refreshing, the new data 1/10 is used in your report, but you want to use data 1/7 10:40 in your report instead.

 

Best regards

Maggie

 

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.