Forum Discussion

powerbricco's avatar
powerbricco
Icon for Advocate I rankAdvocate I
9 months ago
Solved

Insert rows with future months with mean calculus

Dear All,

I have the following table in PowerQuery:

DATEVALUE
28/11/20243765
29/11/20242467
10/12/20241447
03/01/20251097
06/01/20252882
23/01/20253732
07/02/20254448
15/02/20253082


What I need to achieve is to create one row for the future 24 months beginning from the last month on table +1 and every row value should have the mean of the previous months of last year (years before last year should not be included in the mean).

My desired result is as follows:

DATEVALUE 
28/11/20243765 
29/11/20242467 
10/12/20241447 
03/01/20251097values included in the mean calculus
06/01/20252882
23/01/20253732
07/02/20254448
15/02/20253082
31/03/20253048 
30/04/20253048 
31/05/20253048 
30/06/20253048 
31/07/20253048 
31/08/20253048 
30/09/20253048 
31/10/20253048 
30/11/20253048 
31/12/20253048 
31/01/20263048 
28/02/20263048 
31/03/20263048 
30/04/20263048 
31/05/20263048 
30/06/20263048 
31/07/20263048 
31/08/20263048 
30/09/20263048 
31/10/20263048 
30/11/20263048 
31/12/20263048 
31/01/20273048 
28/02/20273048 

 

 

Do you think it is possible?
Thank you a lot

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    9 months ago

    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

3 Replies

  • wardy912's avatar
    wardy912
    Icon for Super User rankSuper User
    9 months ago

    Hi powerbricco 

     

     This can be achieved with the following steps in power query

     

    Get last date of your table:

    LastDate = List.Max(Source[DATE])

     

    Get last year:

    LastYear = Date.Year(LastDate)

     

    Filter rows for last year only:

    LastYearRows = Table.SelectRows(Source, each Date.Year([DATE]) = LastYear)

     

    Calculate Mean:

    MeanValue = List.Average(LastYearRows[VALUE])

     

    Generate a list of future months:

    StartMonth = Date.AddMonths(Date.StartOfMonth(LastDate), 1),
FutureMonths = List.Transform({1..24}, each Date.EndOfMonth(Date.AddMonths(StartMonth, _ - 1)))

     

    Convert this list to a table:

    FutureTable = Table.FromList(FutureMonths, Splitter.SplitByNothing(), {"DATE"}),
FutureTable = Table.AddColumn(FutureTable, "VALUE", each MeanValue)

     

    Combine original table with future table:

    FinalTable = Table.Combine({Source, FutureTable})

     

    This should add 24 new rows with dates as the last day of each month and the calculated mean value

     

    --------------------------------

    I hope this helps, please give kudos and mark as solved if it does!

     

    Connect with me on LinkedIn.

    Subscribe to my YouTube channel for Fabric/Power Platform related content!

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    9 months ago

    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