Insert rows with future months with mean calculus

powerbricco 

pls see if this is what you want

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3JDcAgDETRXnxG8go2taD030YCDpKvT18za4EEMqOQGDRQHx2e9uksKjb8KBOyXGWzVFKk0/atNH8dRSVCcre26ppKjiRXzSzyrRdV2gvPCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, VALUE = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"DATE", type date}}, "en-AU"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"VALUE", Int64.Type}}),
ThisYear = let
MaxYear = List.Max(List.Transform(#"Changed Type"[DATE], Date.Year)),
ThisYear = MaxYear
in
ThisYear,
ThisYearRows = let
ThisYearRows = Table.SelectRows(#"Changed Type", each Date.Year([DATE]) = ThisYear)
in
ThisYearRows,
MeanThisYear = let
MeanThisYear = List.Average(ThisYearRows[VALUE])
in
MeanThisYear,
FutureDates = let
LastDate = List.Max(#"Changed Type"[DATE]),
StartDate = Date.AddMonths(Date.EndOfMonth(LastDate), 1),
FutureDates = List.Transform({0..23}, each Date.EndOfMonth(Date.AddMonths(StartDate, _)))
in
FutureDates,
FutureRowsWithValue = let
FutureRows = Table.FromList(FutureDates, Splitter.SplitByNothing(), {"DATE"}),
FutureRowsWithValue = Table.AddColumn(FutureRows, "VALUE", each MeanThisYear)
in
FutureRowsWithValue,
Custom1 = let
AllRows = Table.Combine({#"Changed Type",FutureRowsWithValue})
in
AllRows
in
Custom1