Power Query Workout 07 - Month - Year Forecast

Difficulty Rating:

Introduction:

You get at the beginning of every month a rolling 12 month forecast where the columns are named: Month1, Month2, Month3 etc.
Month1 is the current month and Month2 the next month and so on.
You have to replace this month enumerating with the actual month - year (e.g. May-2023).

Your task:
Build a dynamic solution independent of the number of months included in the table.
[During the budget planning the number of months is increased to cover the complete next year.]

(NOTE: For more advanced users, attempt to minimize the number of manual changes using relevant M code functions)
Power Query Workout7 Month - Year Forecast
7 Month-Year Forecast.xlsx (19.8 KB)

This workout was posted on Monday May 8, 2023, and the author’s solution will be posted on Sunday May 14, 2023.

3 Likes

Adjunto mi query…

let
Source = Excel.CurrentWorkbook(){[Name=“FC”]}[Content],
Zip = List.Zip({Table.ColumnNames(Source), {“Customer”}&List.Transform({1…Table.ColumnCount(Source)-1}, each Date.ToText(Date.AddMonths(Date.From(DateTimeZone.LocalNow()), _-1), “MMM-yy”, “en-US” ))}),
Sol = Table.RenameColumns(Source, Zip)
in
Sol

1 Like
let
    Source = Excel.CurrentWorkbook(){[Name="FC"]}[Content],
    cab = [
    a = Date.From(DateTimeZone.LocalNow()),
    b = Date.AddMonths(a,11),
    c = List.Distinct(List.Transform({Number.From(a)..Number.From(b)},each Date.ToText(Date.From(_),"MMM-yyyy","en-us"))),
    d = List.RemoveFirstN(Table.ColumnNames(Source),1),
    e = List.Zip({d,c})
][e],
    res = Table.RenameColumns(Source,cab)
in
    res
1 Like
let
    Source = Excel.CurrentWorkbook(){[Name="FC"]}[Content],
    Rename = Table.RenameColumns(Source,let c = Table.ColumnNames(Source) in List.Zip({List.Skip(c), List.Transform({0..List.Count(c)-2},each DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),_ ),"MMM-yy"))}))
in
    Rename
2 Likes

I probably made it more complicated than I should have…

Anyway it made me start to learn List.Accumulate so thanks Matthias, much appreciated!
For reference, I used this post from the community

Code is below. Thanks for the good workout!

let
Source = ExcelW7,
NewHeaders = (Table.FromList(
let
begin = Date.StartOfMonth(DateTime.LocalNow())
in
List.Accumulate({0…List.Count(Table.ColumnNames(Source))-2},
{},
(s,c) => s&{Text.Start(Text.From(Date.MonthName(Date.From((Date.AddMonths(begin,c))))),3) & “-” & Text.End(Text.From(Date.Year(Date.From((Date.AddMonths(begin,c))))),2)})

)),
#“New Columns Table” = Table.RemoveColumns(Table.AddColumn(Table.AddIndexColumn(NewHeaders, “Index”, 1, 1, Int64.Type), “Original”, each Table.ColumnNames(Source){[Index]}),“Index”),
Final = Table.RenameColumns(Source,List.Zip({#“New Columns Table”[Original],#“New Columns Table”[Column1]}))
in
Final

1 Like

Power Query (from Power BI)

let
  FC_Table = Excel.Workbook(
    File.Contents("C:\Users\pavel\Downloads\7 Month-Year Forecast.xlsx"), 
    null, 
    true
  ){[Item = "FC", Kind = "Table"]}[Data], 
  listColumnNames = Table.ColumnNames(FC_Table), 
  listNewColumnNames = {"Customer"}
    & List.Transform(
      {0 .. 11}, 
      each Date.ToText(
        Date.AddMonths(Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())), _), 
        [Format = "MMM yyyy", Culture = "en-US"]
      )
    ), 
  RenameColumns = Table.RenameColumns(FC_Table, List.Zip({listColumnNames, listNewColumnNames}))
in
  RenameColumns
1 Like

It looks like everyone has basically the same idea here but here’s my version written with the intent to break it into easily digestible steps.

let
    Source = Excel.CurrentWorkbook(){[Name="FC"]}[Content],
    MonthCols = List.Skip(Table.ColumnNames(Source)),
    Today = Date.From(DateTime.LocalNow()),
    DatesList = List.Transform(List.Positions(MonthCols), each Date.AddMonths(Today, _)), 
    FormatMonths = List.Transform(DatesList, each Date.ToText(_, [Format="MMM-yy", Culture="en-US"])),
    RenameCols = Table.RenameColumns(Source, List.Zip({MonthCols, FormatMonths}))
in
    RenameCols
1 Like

@Matthias,

LOL - doing the Excel BI challenge for the last 8 months has changed my perspective to the extent that now I think challenges like this are actually easier to do in M than to figure out how to stay within the UI.

Given that this was a 1-star I assume that there is a UI-only solution, but I couldn’t figure it out - I had to resort to some simple M in the two highlighted steps. Is there a straight UI solution?

Code Copied from final step above

let
    Source = Excel.CurrentWorkbook(){[Name="FC"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Promoted Headers", "Customer", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Customer.1", "Customer.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Character Transition",{{"Customer.2", Int64.Type}}),
    #"Subtracted from Column" = Table.TransformColumns(#"Changed Type", {{"Customer.2", each _ - 1, type number}}),
    #"Added Custom" = Table.AddColumn(#"Subtracted from Column", "Today", each DateTime.FixedLocalNow()),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Today", type date}}),
    #"Calculated Start of Month" = Table.TransformColumns(#"Changed Type1",{{"Today", Date.StartOfMonth, type date}}),
    #"Added Custom1" = Table.AddColumn(#"Calculated Start of Month", "Date", each Date.AddMonths([Today] , [Customer.2] )),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "Date", "Date - Copy"),
    #"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column", {{"Date", each Date.MonthName(_), type text}}),
    #"Extracted Year" = Table.TransformColumns(#"Extracted Month Name",{{"Date - Copy", Date.Year, Int64.Type}}),
    #"Split Column by Position" = Table.SplitColumn(#"Extracted Year", "Date", Splitter.SplitTextByPositions({0, 3}, false), {"Date.1", "Date.2"}),
    #"Split Column by Position1" = Table.SplitColumn(Table.TransformColumnTypes(#"Split Column by Position", {{"Date - Copy", type text}}, "en-US"), "Date - Copy", Splitter.SplitTextByPositions({0, 2}, true), {"Date - Copy.1", "Date - Copy.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position1",{"Date.1", "Date - Copy.2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Customer"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Customer.1", "Customer.2", "Today", "Date.2", "Date - Copy.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Customer", "a", "b", "c", "d"}),
    #"Demoted Headers1" = Table.DemoteHeaders(#"Reordered Columns"),
    #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers1"
1 Like

@BrianJ,
Doing it in M is definitely shorter and quicker.

I solved a more complex variant of this for someone a long time ago with multiple steps.
So, I gave it a 1-star because it can be solved with UI support, but after posting I felt remorse about it …
Because using Table.AddColumn is relatively basic, but DateTime.LocalNow and Date.AddMonths are not beginner level. :unamused:

2 Likes

@Matthias ,

Ha! Thanks for the explanation - I was going nuts trying to figure out how you got today’s date in through the UI. :grinning:

It is odd though given all the different date and time options accessible through the UI that today’s date is not one of them…

1 Like

let
Source = Table,
Custom = List.RemoveItems(Table.ColumnNames(Table),{“Customer”}),
NoOfPeriods = List.Count(Custom),
Custom1 =
List.Generate(
()=>[
n=0,
d=DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),n),“MMM-yy”)
],
each [n]<NoOfPeriods,
each [
n=[n]+1,
d=DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),n),“MMM-yy”)
],
each [d]
),
Custom2 =
Table.RenameColumns(
Source,
List.Zip({Custom,Custom1})
)
in
Custom2

1 Like

let
ColNamesOld = List.RemoveItems(Table.ColumnNames(Source), {“Customer”}),
ColNamesNew = List.Transform(List.Transform( List.Transform(List.RemoveItems(Table.ColumnNames(Source), {“Customer”}), each Text.End(, Text.Length()-5)), (IT) => Date.AddMonths(#date(2023, 4, 1), Number.FromText(IT))), each Date.ToText(_,“MMMM-yy”)),
Source = Excel.CurrentWorkbook(){[Name=“FC”]}[Content],
#“Renamed Columns” = Table.RenameColumns(Source,List.Zip({ColNamesOld, ColNamesNew}))
in
#“Renamed Columns”

1 Like

Hello,
thanks to everyone who participated! This time renaming with List.Zip was the clear favorite and for the transformation most used Date.ToText, Date.AddMonths and DateTime.LocalNow().

I’ll show you first an approach with several UI-supported steps, but I admit it right away, it does include Date.AddMonths and DateTime.LocalNow() in a custom column. These are not beginner level functions, but as they are very important, it makes sense to learn them early on.

The second solution uses the mainstream List.Zip approach but with a variant in the month definition and hopefully in an easily understandable way.

Let’s start off with marking the Customer column (resp. all non-month columns) and then on the Transform tab select Unpivot Columns > Unpivot Other Columns:

Right-click on the Attribute column and select Replace Values:
image
Type in Month as Value to Find and leave Replace With empty:

Right-click on the Attribute column and select Change Type > Whole Number:
image

On the Added Column tab select Custom Column:
image
You can name the column Date and we use the Month column for the date definition. DateTime.LocalNow() gives you the current DateTime, we add the number from Month minus 1 and turn the result into a date:
= Date.From(Date.AddMonths(DateTime.LocalNow(), [Attribute]-1))

It would make a lot of sense to wrap this in Date.ToText and format the dates as required:
= Date.ToText(Date.From(Date.AddMonths(DateTime.LocalNow(), [Attribute]-1)), “MMM-yy”, “en-US”)

But let’s assume you don’t know that function and it’s too complex for you. So we go on a UI-supported detour.

Mark the Date column and on the Added Column tab select Date > Month > Name of Month:


Mark the created Month Name column and on the Transform tab select Extract > First Characters:

Type in 3:

For the year mark the Date column and repeat on the Transform tab with Extract > Last Characters and this time type in 2:

Mark first the Month Name column and then the Date column, before right-clicking and selecting Merge Columns:
image
As separator go for Custom and type in a dash. You can keep Merged as column name:

Now we are at the end of the detour, the following 2 steps are also necessary if you use Date.ToText above.

Mark the Attribute column and press delete to get rid of it.

Finally mark the Merged column and on the Transform tab select Pivot Column and as Value Column choose Value:
image

Done! 10 steps if you don’t wrap in Date.ToText(…, “MMM-yy”, “en-US”). 4 less if you do use it.
image

let
    Source = Excel.CurrentWorkbook(){[Name="FC"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Month","",Replacer.ReplaceText,{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.From(Date.AddMonths(DateTime.LocalNow(), [Attribute]-1)) ),
    #"Inserted Month Name" = Table.AddColumn(#"Added Custom", "Month Name", each Date.MonthName([Date], "en-US"), type text),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Extracted First Characters", {{"Date", each Text.End(Text.From(_, "de-DE"), 2), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted Last Characters",{"Month Name", "Date"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value", List.Sum)
in
    #"Pivoted Column"

Quite some effort for the name change. Enough reason to learn and apply a renaming technique with List.Zip. The syntax is quite simple and we “just” need a list of the old column names and a list of the new column names:
= Table.RenameColumns(prevStep, List.Zip( { OldColumnNames, NewColumnNames } ) )

The relevant old column names are all columns which start with Month:
= List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, “Month”))

For building the new column names dynamically you can use Table.ColumnCount, List.Count or List.PositionOf. I’ll transform here the MonthColumns with the same approach as above:
= List.Transform(
MonthColumns,
each Date.ToText(
Date.AddMonths(Date.From(DateTime.LocalNow()), Number.From(Text.AfterDelimiter(_, “Month”))),
“MMM-yy”,
“en-US”
)
)
Now that we have the list of the old column names and the list of the new column names we can reference to the Source step to rename the columns:
= Table.RenameColumns(Source, List.Zip({MonthColumns, NewNames}))

let
  Source = Excel.CurrentWorkbook(){[Name = "FC"]}[Content], 
  MonthColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Month")), 
  NewNames = List.Transform(
    MonthColumns, 
    each Date.ToText(
      Date.AddMonths(Date.From(DateTime.LocalNow()), Number.From(Text.AfterDelimiter(_, "Month"))), 
      "MMM-yy", 
      "en-US"
    )
  ), 
  #"Renamed Columns" = Table.RenameColumns(Source, List.Zip({MonthColumns, NewNames}))
in
  #"Renamed Columns"

That follows a simple three step structure but as you can see in some of the solutions it can even be written more compact.

Try both approaches and make sure to check also some of the other solutions above! => Hope you learn something!

Big thanks to everyone who has blurred or hidden the details! I have taken this now out so that it’s easier to compare solutions.

1 Like
let
    Source = Excel.CurrentWorkbook(){[Name="FC"]}[Content],
    
    // Extarct Variables for Query
    HeaderNames = Table.ColumnNames(Source),                                // Extract column header names         
    HeaderCount = List.Count(HeaderNames) - 1,                                  // Number of columns in table
    cMonth = Date.Month(DateTime.FixedLocalNow()),                        // Current Month number
    cYear = Date.Year(DateTime.FixedLocalNow()),                               // Current Year number

    // Generate New Header List
    Header = List.Generate(
        () =>  [x = 1,                                                                                          // x incrmenting date. Initial starting value for x
                y = #date(cYear,cMonth,1) ],                                                     // y returns date
        each [x] <= HeaderCount,                                                                  // Interate x as long as less than or equal too HeaderCount
        each [x = [x] + 1,                                                                                  // Increment value by 1
              y = Date.AddMonths([y], 1) ],                                                      // Add 1 Month each step
        each  Date.ToText( [y], "MMM-yy","en-US")                                     // Return mmm-yy
    ),
    NewHeader = List.InsertRange( Header, 0, {"Customer"}),              // Insert Customer as first item in generated list
    
    // Change Header in Source
    renamedTable = Table.RenameColumns(
        Source, 
        List.Zip({HeaderNames, NewHeader}))                                           // Change out headers
in
    renamedTable