#### Craigc3814

##### Board Regular

- Joined
- Mar 7, 2016

- Messages
- 211

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"