Last active
March 12, 2019 22:20
-
-
Save ImkeF/aaa9dd0e088f124349d74cad197d8a7f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Bugfix in Step "FirstShare"