Last active
May 3, 2018 09:24
-
-
Save ImkeF/6566422362510cfa312b7f6f2c00cece 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
// 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" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment