Craigc3814
Board Regular
- Joined
- Mar 7, 2016
- Messages
- 217
The current code that I have is FUNCTIONAL (thanks in large part to help from here I apologize for forgetting your @ as you were a tremendous help). What I would like to do is speed up the code that I have for future reference as I assume I will be working on this project even more now that I have shown management that what I did works.
The portion of the code in Bold/Italic below is what I am referencing. I am merging this query 3 times for one particular calculation. If I could instead do some form of a table reference rather than merging I assume the code will run faster? I am working on changing the source data around (cannot share the actual data) so that I can post it with this.
let
Source = Excel.CurrentWorkbook(){[Name="tblProjects"]}[Content],
allColumns.Type = Table.TransformColumnTypes(Source,{{"Budget ID", type any}, {"Project Name", type text}, {"Renamed", type text}, {"Division/Department", type text}, {"Project Manager", type text}, {"Spending Geometry", type text}, {"Status", type text}, {"Est. Start Date", type date}, {"Est. End Date", type date}, {"EAC", type number}}),
activeProjects.Filter = Table.SelectRows(allColumns.Type, each ([Status] = "Deferred" or [Status] = "Future" or [Status] = "In Progress")),
startDate.Sort = Table.Sort(activeProjects.Filter,{{"Est. Start Date", Order.Ascending}}),
monthsDuration.Add = Table.AddColumn(startDate.Sort, "Duration", each ([Est. End Date]-[Est. Start Date])/30.5),
duration.Type = Table.TransformColumnTypes(monthsDuration.Add,{{"Duration", Int64.Type}}),
today.Add = Table.AddColumn(duration.Type, "Today's Date", each DateTime.LocalNow()),
today.Type = Table.TransformColumnTypes(today.Add,{{"Today's Date", type date}}),
#"Calculated Start of Month1" = Table.TransformColumns(today.Type,{{"Today's Date", Date.StartOfMonth, type date}}),
monthsLeft.Add = Table.AddColumn(#"Calculated Start of Month1", "Months Remaining", each if[Est. Start Date]<[#"Today's Date"] then ([Est. End Date]-[#"Today's Date"])/30.5 else [Duration]),
monthsLeft.Type = Table.TransformColumnTypes(monthsLeft.Add,{{"Months Remaining", Int64.Type}}),
pctCPt.Add = Table.AddColumn(monthsLeft.Type, "Percentage Complete", each if 1-([Months Remaining]/[Duration]) < 0 then 0 else 1-([Months Remaining]/[Duration])),
pctCpt.Type = Table.TransformColumnTypes(pctCPt.Add,{{"Percentage Complete", Percentage.Type}}),
pctCpt.Roundoff = Table.TransformColumns(pctCpt.Type,{{"Percentage Complete", each Number.Round(_, 2), Percentage.Type}}),
#"Filtered Rows1" = Table.SelectRows(pctCpt.Roundoff, each [Months Remaining] > 0),
monthsLeft.List = Table.TransformColumns(#"Filtered Rows1", {"Months Remaining", each List.Numbers(_,_,0), type list}),
monthsLeft.ListExpand = Table.ExpandListColumn(monthsLeft.List, "Months Remaining"),
countColumnAdd = Table.AddColumn(monthsLeft.ListExpand, "Instance", each 1),
TableType = Value.Type(Table.AddColumn(countColumnAdd, "Running Count", each null, type number)),
fnGrouping = (MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Count", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Instance],{0},(cumulative,instance) => cumulative & {List.Last(cumulative) + instance})),
RunningCount = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
RunningCount,
rowsGrouped = Table.Group(countColumnAdd, {"Project Name"}, {{"AllData", fnGrouping, TableType}}),
groupsExpanded = Table.ExpandTableColumn(rowsGrouped, "AllData", {"Budget ID", "Project Manager", "Division/Department", "Spending Geometry", "Status", "Renamed", "EAC", "Current Milestone", "EAC Change Category", "Baseline EAC", "Baseline Milestone", "Est. Start Date", "Est. End Date", "Schedule Change Category", "Duration", "Today's Date", "Months Remaining", "Running Count"}, {"Budget ID", "Project Manager", "Division/Department", "Spending Geometry", "Status", "Renamed", "EAC", "Current Milestone", "EAC Change Category", "Baseline EAC", "Baseline Milestone", "Est. Start Date", "Est. End Date", "Schedule Change Category", "Duration", "Today's Date", "Months Remaining", "Running Count"}),
#"Month Math" = Table.AddColumn(groupsExpanded, "Month Math", each [Running Count]-1),
#"Budget Month" = Table.AddColumn(#"Month Math", "Budget Month", each if[Est. Start Date]<[#"Today's Date"] then Date.AddMonths([#"Today's Date"],[Month Math]) else Date.AddMonths([Est. Start Date],[Month Math])),
#"Calculated End of Month" = Table.TransformColumns(#"Budget Month",{{"Budget Month", Date.EndOfMonth, type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Calculated End of Month",{"Running Count", "Month Math"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Project Month", each ([Budget Month]-[Est. Start Date])/30.5),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Project Month", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Percentage Complete", each [Project Month]/[Duration]),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"Budget ID"},Actuals,{"Budget ID"},"Actuals",JoinKind.LeftOuter),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Queries",{{"Percentage Complete", Percentage.Type}}),
#"Rounded Off" = Table.TransformColumns(#"Changed Type1",{{"Percentage Complete", each Number.Round(_, 3), Percentage.Type}}),
#"Expanded Actuals" = Table.ExpandTableColumn(#"Rounded Off", "Actuals", {"Lifetime Actuals"}, {"Actuals.Lifetime Actuals"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Actuals",null,0,Replacer.ReplaceValue,{"Actuals.Lifetime Actuals"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value", "ETC", each [EAC]-[Actuals.Lifetime Actuals]),
#"Sorted Rows" = Table.Sort(#"Added Custom2",{{"Project Name", Order.Ascending}, {"Project Month", Order.Ascending}}),
#"Added Custom3" = Table.AddColumn(#"Sorted Rows", "Previous Month % Complete", each ([Duration]-[Months Remaining])/[Duration]),
#"Rounded Off1" = Table.TransformColumns(#"Added Custom3",{{"Previous Month % Complete", each Number.Round(_, 3), type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Rounded Off1",{{"Previous Month % Complete", Percentage.Type}}),
#"Merged Queries1" = Table.NestedJoin(#"Changed Type2",{"Percentage Complete"},#"Spending Curves",{"Percentage Complete"},"Spending Curves",JoinKind.LeftOuter),
#"Expanded Spending Curves" = Table.ExpandTableColumn(#"Merged Queries1", "Spending Curves", {"Trapezoid Current", "Back Loaded Current", "Front Loaded Current"}, {"Trapezoid Current", "Back Loaded Current", "Front Loaded Current"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Spending Curves",{"Previous Month % Complete"},#"Spending Curves",{"Percentage Complete"},"Spending Curves",JoinKind.LeftOuter),
#"Expanded Spending Curves1" = Table.ExpandTableColumn(#"Merged Queries2", "Spending Curves", {"Trapezoid Current", "Back Loaded Current", "Front Loaded Current"}, {"Trapezoid Current.1", "Back Loaded Current.1", "Front Loaded Current.1"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Spending Curves1", "Prev pct Complete", each ([Project Month]-1)/[Duration]),
#"Rounded Off2" = Table.TransformColumns(#"Added Custom4",{{"Prev pct Complete", each Number.Round(_, 3), type number}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Rounded Off2",{{"Prev pct Complete", Percentage.Type}}),
#"Merged Queries3" = Table.NestedJoin(#"Changed Type3",{"Prev pct Complete"},#"Spending Curves",{"Percentage Complete"},"Spending Curves",JoinKind.LeftOuter),
#"Expanded Spending Curves2" = Table.ExpandTableColumn(#"Merged Queries3", "Spending Curves", {"Trapezoid Current", "Back Loaded Current", "Front Loaded Current"}, {"Trapezoid Current.2", "Back Loaded Current.2", "Front Loaded Current.2"}),
#"Added Custom5" = Table.AddColumn(#"Expanded Spending Curves2", "Month Spend", each if [Spending Geometry]="Back-Loaded" then (1/(1-[Back Loaded Current.1]))*([Back Loaded Current]-[Back Loaded Current.2])*[ETC] else if [Spending Geometry]="Linear" then [ETC]/[Months Remaining] else if [Spending Geometry]="Trapezoid" then (1/(1-[Trapezoid Current.1]))*([Trapezoid Current]-[Trapezoid Current.2])*[ETC] else null),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom5",{{"Month Spend", type number}})
in
#"Changed Type4"
The portion of the code in Bold/Italic below is what I am referencing. I am merging this query 3 times for one particular calculation. If I could instead do some form of a table reference rather than merging I assume the code will run faster? I am working on changing the source data around (cannot share the actual data) so that I can post it with this.
let
Source = Excel.CurrentWorkbook(){[Name="tblProjects"]}[Content],
allColumns.Type = Table.TransformColumnTypes(Source,{{"Budget ID", type any}, {"Project Name", type text}, {"Renamed", type text}, {"Division/Department", type text}, {"Project Manager", type text}, {"Spending Geometry", type text}, {"Status", type text}, {"Est. Start Date", type date}, {"Est. End Date", type date}, {"EAC", type number}}),
activeProjects.Filter = Table.SelectRows(allColumns.Type, each ([Status] = "Deferred" or [Status] = "Future" or [Status] = "In Progress")),
startDate.Sort = Table.Sort(activeProjects.Filter,{{"Est. Start Date", Order.Ascending}}),
monthsDuration.Add = Table.AddColumn(startDate.Sort, "Duration", each ([Est. End Date]-[Est. Start Date])/30.5),
duration.Type = Table.TransformColumnTypes(monthsDuration.Add,{{"Duration", Int64.Type}}),
today.Add = Table.AddColumn(duration.Type, "Today's Date", each DateTime.LocalNow()),
today.Type = Table.TransformColumnTypes(today.Add,{{"Today's Date", type date}}),
#"Calculated Start of Month1" = Table.TransformColumns(today.Type,{{"Today's Date", Date.StartOfMonth, type date}}),
monthsLeft.Add = Table.AddColumn(#"Calculated Start of Month1", "Months Remaining", each if[Est. Start Date]<[#"Today's Date"] then ([Est. End Date]-[#"Today's Date"])/30.5 else [Duration]),
monthsLeft.Type = Table.TransformColumnTypes(monthsLeft.Add,{{"Months Remaining", Int64.Type}}),
pctCPt.Add = Table.AddColumn(monthsLeft.Type, "Percentage Complete", each if 1-([Months Remaining]/[Duration]) < 0 then 0 else 1-([Months Remaining]/[Duration])),
pctCpt.Type = Table.TransformColumnTypes(pctCPt.Add,{{"Percentage Complete", Percentage.Type}}),
pctCpt.Roundoff = Table.TransformColumns(pctCpt.Type,{{"Percentage Complete", each Number.Round(_, 2), Percentage.Type}}),
#"Filtered Rows1" = Table.SelectRows(pctCpt.Roundoff, each [Months Remaining] > 0),
monthsLeft.List = Table.TransformColumns(#"Filtered Rows1", {"Months Remaining", each List.Numbers(_,_,0), type list}),
monthsLeft.ListExpand = Table.ExpandListColumn(monthsLeft.List, "Months Remaining"),
countColumnAdd = Table.AddColumn(monthsLeft.ListExpand, "Instance", each 1),
TableType = Value.Type(Table.AddColumn(countColumnAdd, "Running Count", each null, type number)),
fnGrouping = (MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Count", each null, type number)),
Cumulative = List.Skip(List.Accumulate(Source[Instance],{0},(cumulative,instance) => cumulative & {List.Last(cumulative) + instance})),
RunningCount = Table.FromColumns(Table.ToColumns(Source)&{Cumulative},TableType)
in
RunningCount,
rowsGrouped = Table.Group(countColumnAdd, {"Project Name"}, {{"AllData", fnGrouping, TableType}}),
groupsExpanded = Table.ExpandTableColumn(rowsGrouped, "AllData", {"Budget ID", "Project Manager", "Division/Department", "Spending Geometry", "Status", "Renamed", "EAC", "Current Milestone", "EAC Change Category", "Baseline EAC", "Baseline Milestone", "Est. Start Date", "Est. End Date", "Schedule Change Category", "Duration", "Today's Date", "Months Remaining", "Running Count"}, {"Budget ID", "Project Manager", "Division/Department", "Spending Geometry", "Status", "Renamed", "EAC", "Current Milestone", "EAC Change Category", "Baseline EAC", "Baseline Milestone", "Est. Start Date", "Est. End Date", "Schedule Change Category", "Duration", "Today's Date", "Months Remaining", "Running Count"}),
#"Month Math" = Table.AddColumn(groupsExpanded, "Month Math", each [Running Count]-1),
#"Budget Month" = Table.AddColumn(#"Month Math", "Budget Month", each if[Est. Start Date]<[#"Today's Date"] then Date.AddMonths([#"Today's Date"],[Month Math]) else Date.AddMonths([Est. Start Date],[Month Math])),
#"Calculated End of Month" = Table.TransformColumns(#"Budget Month",{{"Budget Month", Date.EndOfMonth, type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Calculated End of Month",{"Running Count", "Month Math"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Project Month", each ([Budget Month]-[Est. Start Date])/30.5),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Project Month", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Percentage Complete", each [Project Month]/[Duration]),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"Budget ID"},Actuals,{"Budget ID"},"Actuals",JoinKind.LeftOuter),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Queries",{{"Percentage Complete", Percentage.Type}}),
#"Rounded Off" = Table.TransformColumns(#"Changed Type1",{{"Percentage Complete", each Number.Round(_, 3), Percentage.Type}}),
#"Expanded Actuals" = Table.ExpandTableColumn(#"Rounded Off", "Actuals", {"Lifetime Actuals"}, {"Actuals.Lifetime Actuals"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Actuals",null,0,Replacer.ReplaceValue,{"Actuals.Lifetime Actuals"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value", "ETC", each [EAC]-[Actuals.Lifetime Actuals]),
#"Sorted Rows" = Table.Sort(#"Added Custom2",{{"Project Name", Order.Ascending}, {"Project Month", Order.Ascending}}),
#"Added Custom3" = Table.AddColumn(#"Sorted Rows", "Previous Month % Complete", each ([Duration]-[Months Remaining])/[Duration]),
#"Rounded Off1" = Table.TransformColumns(#"Added Custom3",{{"Previous Month % Complete", each Number.Round(_, 3), type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Rounded Off1",{{"Previous Month % Complete", Percentage.Type}}),
#"Merged Queries1" = Table.NestedJoin(#"Changed Type2",{"Percentage Complete"},#"Spending Curves",{"Percentage Complete"},"Spending Curves",JoinKind.LeftOuter),
#"Expanded Spending Curves" = Table.ExpandTableColumn(#"Merged Queries1", "Spending Curves", {"Trapezoid Current", "Back Loaded Current", "Front Loaded Current"}, {"Trapezoid Current", "Back Loaded Current", "Front Loaded Current"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded Spending Curves",{"Previous Month % Complete"},#"Spending Curves",{"Percentage Complete"},"Spending Curves",JoinKind.LeftOuter),
#"Expanded Spending Curves1" = Table.ExpandTableColumn(#"Merged Queries2", "Spending Curves", {"Trapezoid Current", "Back Loaded Current", "Front Loaded Current"}, {"Trapezoid Current.1", "Back Loaded Current.1", "Front Loaded Current.1"}),
#"Added Custom4" = Table.AddColumn(#"Expanded Spending Curves1", "Prev pct Complete", each ([Project Month]-1)/[Duration]),
#"Rounded Off2" = Table.TransformColumns(#"Added Custom4",{{"Prev pct Complete", each Number.Round(_, 3), type number}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Rounded Off2",{{"Prev pct Complete", Percentage.Type}}),
#"Merged Queries3" = Table.NestedJoin(#"Changed Type3",{"Prev pct Complete"},#"Spending Curves",{"Percentage Complete"},"Spending Curves",JoinKind.LeftOuter),
#"Expanded Spending Curves2" = Table.ExpandTableColumn(#"Merged Queries3", "Spending Curves", {"Trapezoid Current", "Back Loaded Current", "Front Loaded Current"}, {"Trapezoid Current.2", "Back Loaded Current.2", "Front Loaded Current.2"}),
#"Added Custom5" = Table.AddColumn(#"Expanded Spending Curves2", "Month Spend", each if [Spending Geometry]="Back-Loaded" then (1/(1-[Back Loaded Current.1]))*([Back Loaded Current]-[Back Loaded Current.2])*[ETC] else if [Spending Geometry]="Linear" then [ETC]/[Months Remaining] else if [Spending Geometry]="Trapezoid" then (1/(1-[Trapezoid Current.1]))*([Trapezoid Current]-[Trapezoid Current.2])*[ETC] else null),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom5",{{"Month Spend", type number}})
in
#"Changed Type4"
