Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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:
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
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:
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
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.
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"?
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,
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
35 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
45 | |
25 | |
16 | |
14 | |
14 |