Allocation.StretchAndCompress.pq

let func =   
  (PeriodStartForecast as number, PeriodEndForecast as number, OriginalForecast as table) =>

let
/* Debug Parameters
    PeriodEndForecast = PeriodEndForecast,    
    PeriodStartForecast = PeriodStartForecast,
    OriginalForecast = LU_Original_Forecast_Data,
*/ 

    ForecastDuration = PeriodEndForecast-PeriodStartForecast+1,
    OriginalForecast = OriginalForecast,
    
    // Preparing the input table
    #"Transposed Table" = Table.Transpose(OriginalForecast),
    #"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column1", "Amount"}}),
    AddIndex = Table.AddIndexColumn(#"Renamed Columns", "Period", 1, 1),
    
    // Remove leading and trailing nulls and fill up empty spaces to cater for non-contingous series
    RemoveLeadingNulls = Table.RemoveFirstN(AddIndex, each [Amount]=null),
    RemoveTrainlingNulls = Table.RemoveLastN(RemoveLeadingNulls, each [Amount]=null),
    FillNulls = Table.ReplaceValue(RemoveTrainlingNulls,null,0,Replacer.ReplaceValue,{"Amount"}),
    AddRelativePeriod = Table.AddIndexColumn(FillNulls, "RelativePeriod", 1, 1),
    
    // Calculating the periodic factors
    PeriodFactor = Table.RowCount(AddRelativePeriod) / ForecastDuration,
    Intervals = 1/PeriodFactor,
    
    // Number of intervals with rounding, as they have to be full numbers
    ListOfIntervals = Table.AddColumn(AddRelativePeriod, "NewPeriods", each {Number.RoundDown([RelativePeriod]*Intervals-Intervals)+1..Number.RoundUp([RelativePeriod]*Intervals)}),
    
    // Share per full interval
    ShareAmount = Table.AddColumn(ListOfIntervals, "ShareAmount", each [Amount]/Intervals),
    
    // Exact new Start- & End-Dates
    NewEnds = Table.AddColumn(ShareAmount, "NewEnds", each [RelativePeriod]*Intervals),
    NewStart = Table.AddColumn(NewEnds, "NewStart", each try NewEnds[NewEnds]{[RelativePeriod]-2} otherwise 0),
    
    // Start- & End-Shares
    FirstShare = Table.AddColumn(NewStart, "FirstShare", each List.Min({List.First([NewPeriods]), [NewEnds]})-[NewStart]),
    LastShare = Table.AddColumn(FirstShare, "LastShare", each [NewEnds]-(List.Last([NewPeriods])-1)),
    
    // Combine Start- & End-Shares with full intervals in between. Depending on number of intervals in original interval
    ListOfShares = Table.AddColumn(LastShare, "ListOfShares", each if List.Count([NewPeriods])=1 then {List.Min({[FirstShare], [LastShare]})} 
  							      else if List.Count([NewPeriods])=2 then {[FirstShare]} & {[LastShare]} 
												 else {[FirstShare]} & List.Repeat({1}, List.Count([NewPeriods])-2) & {[LastShare]}),
    
    // Combining the new periods with the shares
    PeriodsAndShares = Table.AddColumn(ListOfShares, "PeriodsAndShares", each List.Zip({[NewPeriods], [ListOfShares]})),
    #"Expanded PeriodsAndShares" = Table.ExpandListColumn(PeriodsAndShares, "PeriodsAndShares"),
    ToRecord = Table.AddColumn(#"Expanded PeriodsAndShares", "Record", each [NewRelativePeriod = [PeriodsAndShares]{0}, Share = [PeriodsAndShares]{1}]),
    ExpandRecord = Table.ExpandRecordColumn(ToRecord, "Record", {"NewRelativePeriod", "Share"}, {"NewRelativePeriod", "Share"}),
    
    // New Amount per interval
    NewAmount = Table.AddColumn(ExpandRecord, "NewAmount", each [ShareAmount]*[Share]),
    
    // Group on Days and sum amount
    GroupOnDays = Table.Group(NewAmount, {"NewRelativePeriod"}, {{"NewAmount", each List.Sum([NewAmount]), type number}}),
    
    // Add Start date to convert relative dates to actual dates
    NewRelativePeriod = Table.TransformColumns(GroupOnDays, {{"NewRelativePeriod", each _ + PeriodStartForecast -1, type number}}),
    
    // Lookup with original table to create full list of dates
    MergeWithSource = Table.NestedJoin(AddIndex,{"Period"},NewRelativePeriod,{"NewRelativePeriod"},"Periods",JoinKind.FullOuter),
    ExpandAllPeriods = Table.ExpandTableColumn(MergeWithSource, "Periods", {"NewAmount"}, {"NewAmount"}),
    
    // Cleanup and transpose to target formatting
    Cleanup = Table.RemoveColumns(ExpandAllPeriods,{"Amount"}),
    SortPeriod = Table.Sort(Cleanup,{{"Period", Order.Ascending}}),
    Transpose = Table.Transpose(SortPeriod),
    PromoteHeaders = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
in
    PromoteHeaders  ,
documentation = [
Documentation.Name =  " Allocation.StretchAndCompress ",
Documentation.Description = " Allocates values by stretching or compressing a series of values over time ",
Documentation.LongDescription = " Allocates values by stretching or compressing a series of values over time.  The first 2 parameters take numbers for Start and End of the new period and  <code>OriginalForecast</code>  is a table with one row for the original values. More details see here: https://wp.me/p6lgsG-Q6 . ",
Documentation.Category = " Allocation ",
Documentation.Source = " https://wp.me/p6lgsG-Q6 . ",
Documentation.Version = " 1.3 (2018-05-02: Bugfix in Step FirstShare) ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . ",
Documentation.Examples = {[Description =  "  ",
Code = "  ",
Result = "  "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))  

Bugfix in Step "FirstShare"