Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
useruser3
Regular Visitor

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

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.

Screenshot 2023-06-07 175517.png

 

Thanks!

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

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

ronrsnfld_0-1686250734408.png

 

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

ronrsnfld_1-1686250906575.png

 

 

 

View solution in original post

5 REPLIES 5
ronrsnfld
Super User
Super User

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

ronrsnfld_0-1686250734408.png

 

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

ronrsnfld_1-1686250906575.png

 

 

 

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?

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
Regular Visitor

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
Resolver II
Resolver II

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,

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors