Stretching and Compressing Time Series with Power Query and Power BI

The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead:

The Challenge

Stretching or compressing a forecast so that the proportion of the original series will be maintained:

The Function

This is not so easy mathematically (as the solution-blogpost explains in detail), so the code for the function isn’t short as well:


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))

view raw

Allocation.StretchAndCompress.pq

hosted with ❤ by GitHub

How it works

The first 2 arguments are the new start- & end-dates and have to be entered as whole numbers of their relative position. The 3rd argument takes the table of the original forecast with one row containing all the values. The column names can have any value, as they will be ignored by the function.

You can download the file here: Allocation_StretchAndCompress

If you adjust the input values, the results of the native Excel-version will update immediately, while the results from Power Query have to be refreshed. So before doing so, you will see a lot of warnings in the reconciliation line:

But once refreshed (i.e.: Data -> Queries & Connections -> Refresh All or checking the table or query and then right-click your mouse -> Refresh), everything should be green (unless minor rounding differences occur).

Once you’ve loaded the table into your worksheet for the first time, your column widths might be all over the place (because of the tables default-setting is adjusting column width automatically (1)). Therefore change the settings in the properties like so:

The second adjustment is also very important, as this prevents existing content in your sheet to be shifted to the right, once this table should get larger. So make sure to adjust this setting as well.

The file also contains a query (“StretchingAndCompressing_FollowAlong”) where you can follow along every step of the function to see what’s happening. If you want to learn more about allocation techniques, please join our next User Group Meeting for Power BI for Accountants.

Reflection

Some advantages I see:

  • takes less footprint in the Excel-sheets
  • less error-prone, as there are no formulas in the worksheet that have to be protected
  • very dynamic, as more or less columns will be produced automatically
  • very quick to implement: Just copy the function, paste to new worksheet and search a place for the results. No need to enter and expand formulas and connect to existing values in the spreadsheet itself.
  • works in Power BI as well 😉

What did I miss? Please let me know in the comments!!!

Edit 1-May-2018: Fixed formula to cater for non-continuous-series as well. Leading and trailing nulls will be eliminated/ignored, but all nulls between the first and the last value will be considered as 0:

Enjoy & stay queryious 😉

Disclaimer: As so often, Bill Szysz came up with a more elegant version that you shouldn’t miss 🙂 :


// 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"

view raw

Allocation.StretchAndCompressBillSzysz.pq

hosted with ❤ by GitHub

 

Comments (2) Write a comment

Leave a Reply