Power Query: Duplicate Rows, MONTH(TODAY()), and Add to Month For Duplicated Rows

[Jpalaci1]

I successfully transformed my data in Power Query and in the steps to get the data almost to where I need it. 

I'd like to take step 1a, step 2, and step 3 and put it into a final output in step 4.

Step1: How to do MONTH(TODAY()) and MONTH(TODAY())-1 in PQ?

• Filter1: IF statement that looks at column F and returns keeps or delete based on the the current month. In Excel I'd use a IF([@Month]<=MONTH(TODAY(),"","Delete"). Not sure how to replicate MONTH(TODAY()) in PQ. This leads into step 1a where I only find costs that are non-zero/actual as the raw data comes to me with all month (actuals on months past and 0 for future months)
• Filter2: IF([@Month]=MONTH(TODAY()-1,"Roll Forward",""). Again, not sure how to do MONTH(TODAY()) in PQ. This leads to step 1b that gives me the last month with actuals so I can roll this forward for a forecast for the remaining months.
• These are only columns to help get me to my next steps.

Step 2 & 3: How to repeat the result in step 1b to duplicate rows?

• Step 2: I take the roll forward from step 1b but need duplicate the rows but by the remaining month (12-[MONTH]). In my example Month is 8 so 12-8=4 to duplicate every line 4 times and then Month+N; that is, roll forward for 9, 10, 11, and 12. I'm not sure how to make this dynamic where 12-[MONTH] would give me while fitting the month
• Step 3: similar but much simpler where it just duplicates a fixed 11 times and starts at Month 1 then fills into Month 12

Step 4: How to combine step 1a, step 2, and step 3 into one table?

[#2]

Hello @Jpalaci1 !

Step 1 :

1.  Filter 1 :
   • I recommend you to add TODAY() formula in a cell in your worksheet. Then, load this range in Power Query (Select your cell, click on "Data" > "From table"). Today date will be now loaded into Power Query.
   • To use it properly, you will have to add "{0}[Column1]" (drill down) to get a value and not a table. It should look like this "Date.From(Excel.CurrentWorkbook(){[Name="Today_date"]}[Content]{0}[Column1])". 
   • Rename it "Today_date" and use this date for your filter > Table.SelectRows(Step, each [Month]>= Date.Month(Today_date))
2. Filter 2 :
   • Use Date.Month(Today_date) to help you perform this filter

Step 2 & 3 :

1. Step 2 :
   • if you want to duplicate rows for each month, you can create for each row a list which contains month number from month +1 to 12. Example : {1..12} creates a list from 1 to 12.
   • You can use "AddColumn = Table.AddColumn(PreviousStep, "List", each {[Month]+1..12})". You will have a list from Month +1 to 12 in each Row.
   • Then, Expand this new column by using "ExpandColumn = = Table.ExpandListColumn(AddColumn, "List").
2. Step 3 :
   • I don't understand what you would like to do. You can use the same list creation as explained in step 2 to duplicate rows (with {1..12}).

In order to append step 1a, step 2 and step 3 into one table, you can simply use Table.Combine (more info here : Table.Combine - PowerQuery M | Microsoft Docs)

Step4 = Table.Combine({"Step1", "Step2", "Step3")}

Hope it will help,

Nicolas

[#3]

@Jpalaci1 

Practically the same as @NicolasTld  explained, in terms of generated scripts

Data

let
    thisMonth = Date.StartOfMonth( Date.From( DateTime.LocalNow() ) ),
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    dataMonth = Table.AddColumn(Source, "dataMonth", each #date([Year], [Month], 1)),
    Filter1 = Table.AddColumn(dataMonth, "Filter1", each [dataMonth] < thisMonth),
    Filter2 = Table.AddColumn(Filter1, "Filter2", each [dataMonth] = thisMonth)
in
    Filter2

Previous:

let
    Source = Data,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Filter1] = true)),
    RemoveFilters = Table.RemoveColumns(#"Filtered Rows",{"dataMonth", "Filter1", "Filter2"})
in
    RemoveFilters

Current:

let
    Source = Data,
    FilterCurrent = Table.SelectRows(Source, each ([Filter2] = true)),
    RemoveFilters = Table.RemoveColumns(FilterCurrent,{ "dataMonth", "Filter1", "Filter2"})
in
    RemoveFilters

Future:

let
    thisMonth = Date.StartOfMonth( Date.From( DateTime.LocalNow() ) ),
    thisMonthNumber = Date.Month( thisMonth ),
    otherMonths = {thisMonthNumber+1 .. 12},
    Source = Data,
    FilterCurrent = Table.SelectRows(Source, each ([Filter2] = true)),
    RemoveFilters = Table.RemoveColumns(FilterCurrent,{"Month", "dataMonth", "Filter1", "Filter2"}),
    AddMonths = Table.AddColumn(RemoveFilters, "Month", each otherMonths),
    Duplicate = Table.ExpandListColumn(AddMonths, "Month")
in
    Duplicate

fullYear:

let
    Source = Table.Combine({Previous, Current, Future})
in
    Source

[#4 · Accepted solution]

@Jpalaci1 

Oops, forgot to check if we are in December duplication is not required. As variant you may add if ... then ... else.

[#5]

Thank you for the help. This helped!

[#6]

Thank you so much!

[#7]

With pleasure !

You should better use "Date.From( DateTime.LocalNow() )" as Sergei proposed instead of load a named range with TODAY formula. I forgot this function

Have a nice day

Nicolas

[#8]

@Jpalaci1 , you are welcome