Allocation.StretchAndCompressBillSzysz.pq

// Function from Bill Szysz:
(NPStart as number, NPEnd as number, OriginForecast as table) as table =>

let
// Transforming Original Forecast
    OriginalForecast = Record.ToTable(OriginForecast{0}),
    #"Removed Top Rows" = Table.RemoveFirstN(OriginalForecast, each [Value]=null),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",each [Value] = null),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Bottom Rows",null,0,Replacer.ReplaceValue,{"Value"}),
    AddedListColumn = Table.AddColumn(#"Replaced Value", "Shared Parts", each {0..NPEnd-NPStart}),
    #"Expanded {0}" = Table.ExpandListColumn(AddedListColumn, "Shared Parts"),
    AddedNewPeriodsColumn = Table.AddIndexColumn(#"Expanded {0}", "NewPeriods", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(AddedNewPeriodsColumn, {{"NewPeriods", each Number.IntegerDivide(_, ActualDuration), Int64.Type}}),
    AddedNewValueColumn = Table.AddColumn(#"Integer-Divided Column", "NewValue", each [Value]/ForecastDuration),
    #"Grouped Rows" = Table.Group(AddedNewValueColumn, {"NewPeriods"}, {{"Sum", each List.Sum([NewValue]), type number}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Grouped Rows",0,null,Replacer.ReplaceValue,{"Sum"}),
    ReadyToMerge = Table.TransformColumns(#"Replaced Value1", {{"NewPeriods", each _ + NPStart, type number}}),

// Proper Periods and parameters
    Periods = Table.FromColumns( {{1..List.Max({NPEnd, Table.RowCount(OriginalForecast)})}}, {"Periods"}),
    ActualDuration = Table.RowCount(#"Replaced Value"),
    ForecastDuration = NPEnd - NPStart + 1,

// Merging, Sorting, Pivoting
    #"Merged Queries" = Table.NestedJoin(Periods,{"Periods"},ReadyToMerge,{"NewPeriods"},"New",JoinKind.LeftOuter),
    #"Expanded {0}1" = Table.ExpandTableColumn(#"Merged Queries", "New", {"Sum"}, {"Sum"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded {0}1",{{"Periods", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows1",{{"Periods", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", #"Changed Type"[Periods], "Periods", "Sum")
in
    #"Pivoted Column"