Rolling 12 months forecast (power query) , setting month equal to previous

[useruser3]

Looking to create rolling forecast where 2023 is pulled from the forecast database and 2024 months are just set equal to December. Is there a way to do this in power query, w/o using DAX? I have figured out how to have dymanic columns tacked on as the months cycle through, but not sure how to go about pulling in the dec RR for the now empty 2024 columns.

Thanks!

[ronrsnfld · Accepted solution]

You may need to alter the data type for your data, as you don't show what it is, but it is not relevant to the basic code:

• Assumption is that your original table ends with the "Dec 2023" months or whatever year
• Demote the headers and transpose the table
  • Column1 will now be the dates
• Add Dates to Column 1 for the next year
• The transposed table will now have nulls below the December date pull in all of the other columns.
• Fill Down
• Transpose back
  • Promote first row to headers
  • set the data types

Original Data Table

let

//Read in data table and set types
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, Int64.Type})),

//Demote the headers and transpose the table
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),

//Add the "next year" to the Date columns (Column1)
    #"Next Year" = 
        let 
            #"Last Date" = Date.From(List.Last(#"Transposed Table"[Column1])),
            next = List.Accumulate(
                        {2..12},
                        {Date.AddMonths(#"Last Date",1)}, 
                        (state, current)=>         
                        state & {Date.AddMonths(#"Last Date",current)}),

        //convert list of dates to text as they will be column headers
            #"To Text" = List.Transform(next, each Date.ToText(_,"M/d/yyyy")),
            #"Add Rows" = Table.FromColumns(
                    {#"Transposed Table"[Column1] & #"To Text"} & 
                   Table.ToColumns(Table.RemoveColumns(#"Transposed Table", List.First(Table.ColumnNames(#"Transposed Table"),1))))        
        in 
            #"Add Rows",

//Fill Down, then transpose back
    #"Fill Down" = Table.FillDown(#"Next Year", Table.ColumnNames(#"Next Year")),
    #"Transposed Table1" = Table.Transpose(#"Fill Down"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers", List.Transform(Table.ColumnNames(#"Promoted Headers"), each {_, Int64.Type}))
in
    #"Changed Type1"

Results

[useruser3]

thanks! if i have data to the left can i just change [column1] to whatever column number it will then be or will that break things?

[ronrsnfld]

You shouldn't have to change anything other than possibly the data type transformations. If you are seeing errors, I'd suggest deleting the data type transformation steps and figuring things out from there.

The dates will still be in Column1 of the transposed table.

[useruser3]

hi thanks! but the "dec rr" would be dynamic number, as in set equal to what the runrate is for december, not text. not sure i'm following this part of it as i see the hardcoded"dec rr"?

[JamesRobson]

Right after spending far to long on this 😁  I think I have a solution however it is going to need 3 sperate query to do so (someone better than me can probably do it in one)

It will take your source data of X number of months remainign in the current year and make it up to a total of 12 Months (so May 2023 to May 2024) and anything in the next year (dynamic so wont need to change each year) put "dec rr" in each cell.

Query 1 "Original Data"

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Appended Query" = Table.Combine({Source, NewColumns}),
#"Replaced Value" = Table.ReplaceValue(#"Appended Query",null,"dec rr",Replacer.ReplaceValue,Table.ColumnNames(NewColumns))
in
#"Replaced Value"

Query 2 "Next Year"

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzBUitWBsIzgLGM4ywTOMoWzzOAsczjLAs6yhLEMDeAsuB2GQDtiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NextYear", each Date.AddYears([Column1],1), type date),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NextYear", "Column1"}})
in
#"Renamed Columns"

Query 3 "NewColumns"

let
MyTable = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
ColumnNames = Table.ColumnNames(MyTable),
#"Converted to Table" = Table.FromList(ColumnNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Appended Query" = Table.Combine({#"Changed Type", NextYear}),
#"Added Index" = Table.AddIndexColumn(#"Appended Query", "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 1 or [Index] = 2 or [Index] = 3 or [Index] = 4 or [Index] = 5 or [Index] = 6 or [Index] = 7 or [Index] = 8 or [Index] = 9 or [Index] = 10 or [Index] = 11 or [Index] = 12)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each Date.IsInNextYear([Column1])),
#"Transposed Table" = Table.Transpose(#"Filtered Rows1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
#"Promoted Headers"

Give that a go and edit your Source to wherever as I just used a manually created excel table for ease so I could test removing/adding columns in current year data and still only getting 12 columns back.

Thanks,