PowerQuery - Forecast from table

Question

I am trying to create a forecast (single table) for departments to input their assumptions on spending in a single table. Instead of entering amounts for every single month, I would like the user to enter the amount, frequency, start date, and end date for each category. To illustrate, see below the table with some sample data.

This is the result in Power Query (or Power BI) I am trying to get, which is my understanding of how to be able to run date slicers and filters in a Power BI model when comparing against actuals.

If this can't be done with DAX and instead must be done in excel (through look up formulas), how would you structure the formula?

Answer 1

Here is a PQ example that creates what you show as your desired table given what you show as your input:

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to better understand the algorithm

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"G/L", Int64.Type}, {"Dimension", type text}, {"Description", type text}, 
        {"Amount", Int64.Type}, {"Repeat Every", type text}, {"Start Date", type date}, {"End Date", type date}}),

//Last possible date as Today + 5 years (to end of month)
    lastDt = Date.EndOfMonth(Date.AddYears(Date.From(DateTime.FixedLocalNow()),5)),

//Generate list of all possible dates for a given row using List.Generate function
    allDates = Table.AddColumn(#"Changed Type", "allDates", each let 
            lastDate = List.Min({lastDt,[End Date]}),
            intvl = {1,3,6}{List.PositionOf({"Monthly","Quarterly","Semi Annual"},[Repeat Every])}
        in 
            List.Generate(
                ()=> [Start Date],
                each _ <= lastDate,
                each Date.EndOfMonth(Date.AddMonths(_,intvl)))),

//Remove unneeded columns and expand the list of dates
    #"Removed Columns" = Table.RemoveColumns(allDates,{"Repeat Every", "Start Date", "End Date"}),
    #"Expanded allDates" = Table.ExpandListColumn(#"Removed Columns", "allDates"),

//Sort to get desired output
//  Date column MUST be sorted to ensure correct order when pivoted
//  Other columns sorted alphanumerically, but could change the sort to reflect original order if preferred.
    #"Sorted Rows" = Table.Sort(#"Expanded allDates",{
        {"allDates", Order.Ascending}, 
        {"G/L", Order.Ascending}, 
        {"Dimension", Order.Ascending}}),

//Pivot the date column with no aggregation
    #"Pivoted Column" = Table.Pivot(
            Table.TransformColumnTypes(#"Sorted Rows", {
                {"allDates", type text}}, "en-US"), 
            List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"allDates", type text}}, "en-US")[allDates]), 
                "allDates", "Amount")
in
    #"Pivoted Column"

Original Data

Results