SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2802621%22%20slang%3D%22en-US%22%3EPower%20Query%3A%20Duplicate%20Rows%2C%20MONTH(TODAY())%2C%20and%20Add%20to%20Month%20For%20Duplicated%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2802621%22%20slang%3D%22en-US%22%3E%3CP%3EI%20successfully%20transformed%20my%20data%20in%20Power%20Query%20and%20in%20the%20steps%20to%20get%20the%20data%20almost%20to%20where%20I%20need%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20take%20step%201a%2C%20step%202%2C%20and%20step%203%20and%20put%20it%20into%20a%20final%20output%20in%20step%204.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep1%3A%20How%20to%20do%20MONTH(TODAY())%20and%20MONTH(TODAY())-1%20in%20PQ%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EFilter1%3A%20IF%20statement%20that%20looks%20at%20column%20F%20and%20returns%20keeps%20or%20delete%20based%20on%20the%20the%20current%20month.%20In%20Excel%20I'd%20use%20a%20IF(%5B%40Month%5D%26lt%3B%3DMONTH(TODAY()%2C%22%22%2C%22Delete%22).%20Not%20sure%20how%20to%20replicate%20MONTH(TODAY())%20in%20PQ.%20This%20leads%20into%20step%201a%20where%20I%20only%20find%20costs%20that%20are%20non-zero%2Factual%20as%20the%20raw%20data%20comes%20to%20me%20with%20all%20month%20(actuals%20on%20months%20past%20and%200%20for%20future%20months)%3C%2FLI%3E%3CLI%3EFilter2%3A%20IF(%5B%40Month%5D%3DMONTH(TODAY()-1%2C%22Roll%20Forward%22%2C%22%22).%20Again%2C%20not%20sure%20how%20to%20do%20MONTH(TODAY())%20in%20PQ.%20This%20leads%20to%20step%201b%20that%20gives%20me%20the%20last%20month%20with%20actuals%20so%20I%20can%20roll%20this%20forward%20for%20a%20forecast%20for%20the%20remaining%20months.%3C%2FLI%3E%3CLI%3EThese%20are%20only%20columns%20to%20help%20get%20me%20to%20my%20next%20steps.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%202%20%26amp%3B%203%3A%20How%20to%20repeat%20the%20result%20in%20step%201b%20to%20duplicate%20rows%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EStep%202%3A%20I%20take%20the%20roll%20forward%20from%20step%201b%20but%20need%20duplicate%20the%20rows%20but%20by%20the%20remaining%20month%20(12-%5BMONTH%5D).%20In%20my%20example%20Month%20is%208%20so%2012-8%3D4%20to%20duplicate%20every%20line%204%20times%20and%20then%20Month%2BN%3B%20that%20is%2C%20roll%20forward%20for%209%2C%2010%2C%2011%2C%20and%2012.%20I'm%20not%20sure%20how%20to%20make%20this%20dynamic%20where%2012-%5BMONTH%5D%20would%20give%20me%20while%20fitting%20the%20month%3C%2FLI%3E%3CLI%3EStep%203%3A%20similar%20but%20much%20simpler%20where%20it%20just%20duplicates%20a%20fixed%2011%20times%20and%20starts%20at%20Month%201%20then%20fills%20into%20Month%2012%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EStep%204%3A%20How%20to%20combine%20step%201a%2C%20step%202%2C%20and%20step%203%20into%20one%20table%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2802621%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2802681%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20Duplicate%20Rows%2C%20MONTH(TODAY())%2C%20and%20Add%20to%20Month%20For%20Duplicated%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2802681%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%20%3A%3C%2FP%3E%3COL%3E%3CLI%3E%26nbsp%3BFilter%201%20%3A%3CUL%3E%3CLI%3EI%20recommend%20you%20to%20add%20TODAY()%20formula%20in%20a%20cell%20in%20your%20worksheet.%20Then%2C%20load%20this%20range%20in%20Power%20Query%20(Select%20your%20cell%2C%20click%20on%20%22Data%22%20%26gt%3B%20%22From%20table%22).%20Today%20date%20will%20be%20now%20loaded%20into%20Power%20Query.%3C%2FLI%3E%3CLI%3ETo%20use%20it%20properly%2C%20you%20will%20have%20to%20add%20%22%7B0%7D%5BColumn1%5D%22%20(drill%20down)%20to%20get%20a%20value%20and%20not%20a%20table.%20It%20should%20look%20like%20this%20%22Date.From(Excel.CurrentWorkbook()%7B%5BName%3D%22Today_date%22%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D)%22.%26nbsp%3B%3C%2FLI%3E%3CLI%3ERename%20it%20%22Today_date%22%20and%20use%20this%20date%20for%20your%20filter%20%26gt%3B%20Table.SelectRows(Step%2C%20each%20%5BMonth%5D%26gt%3B%3D%20Date.Month(Today_date))%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3EFilter%202%20%3A%3CUL%3E%3CLI%3EUse%20Date.Month(Today_date)%20to%20help%20you%20perform%20this%20filter%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep%202%20%26amp%3B%203%20%3A%3C%2FP%3E%3COL%3E%3CLI%3EStep%202%20%3A%3CUL%3E%3CLI%3Eif%20you%20want%20to%20duplicate%20rows%20for%20each%20month%2C%20you%20can%20create%20for%20each%20row%20a%20list%20which%20contains%20month%20number%20from%20month%20%2B1%20to%2012.%20Example%20%3A%20%7B1..12%7D%20creates%20a%20list%20from%201%20to%2012.%3C%2FLI%3E%3CLI%3EYou%20can%20use%20%22AddColumn%20%3D%20Table.AddColumn(PreviousStep%2C%20%22List%22%2C%20each%20%7B%5BMonth%5D%2B1..12%7D)%22.%20You%20will%20have%20a%20list%20from%20Month%20%2B1%20to%2012%20in%20each%20Row.%3C%2FLI%3E%3CLI%3EThen%2C%20Expand%20this%20new%20column%20by%20using%20%22ExpandColumn%20%3D%26nbsp%3B%3D%20Table.ExpandListColumn(AddColumn%2C%20%22List%22).%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3EStep%203%20%3A%3CUL%3E%3CLI%3EI%20don't%20understand%20what%20you%20would%20like%20to%20do.%20You%20can%20use%20the%20same%20list%20creation%20as%20explained%20in%20step%202%20to%20duplicate%20rows%20(with%20%7B1..12%7D).%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20order%20to%20append%20step%201a%2C%20step%202%20and%20step%203%20into%20one%20table%2C%20you%20can%20simply%20use%20Table.Combine%20(more%20info%20here%20%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Ffr-fr%2Fpowerquery-m%2Ftable-combine%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ETable.Combine%20-%20PowerQuery%20M%20%7C%20Microsoft%20Docs%3C%2FA%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep4%20%3D%20Table.Combine(%7B%22Step1%22%2C%20%22Step2%22%2C%20%22Step3%22)%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20will%20help%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENicolas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2804343%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20Duplicate%20Rows%2C%20MONTH(TODAY())%2C%20and%20Add%20to%20Month%20For%20Duplicated%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPractically%20the%20same%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1032856%22%20target%3D%22_blank%22%3E%40NicolasTld%3C%2FA%3E%26nbsp%3B%20explained%2C%20in%20terms%20of%20generated%20scripts%3C%2FP%3E%0A%3CP%3EData%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20thisMonth%20%3D%20Date.StartOfMonth(%20Date.From(%20DateTime.LocalNow()%20)%20)%2C%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Data%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20dataMonth%20%3D%20Table.AddColumn(Source%2C%20%22dataMonth%22%2C%20each%20%23date(%5BYear%5D%2C%20%5BMonth%5D%2C%201))%2C%0A%20%20%20%20Filter1%20%3D%20Table.AddColumn(dataMonth%2C%20%22Filter1%22%2C%20each%20%5BdataMonth%5D%20%26lt%3B%20thisMonth)%2C%0A%20%20%20%20Filter2%20%3D%20Table.AddColumn(Filter1%2C%20%22Filter2%22%2C%20each%20%5BdataMonth%5D%20%3D%20thisMonth)%0Ain%0A%20%20%20%20Filter2%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EPrevious%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Data%2C%0A%20%20%20%20%23%22Filtered%20Rows%22%20%3D%20Table.SelectRows(Source%2C%20each%20(%5BFilter1%5D%20%3D%20true))%2C%0A%20%20%20%20RemoveFilters%20%3D%20Table.RemoveColumns(%23%22Filtered%20Rows%22%2C%7B%22dataMonth%22%2C%20%22Filter1%22%2C%20%22Filter2%22%7D)%0Ain%0A%20%20%20%20RemoveFilters%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ECurrent%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Data%2C%0A%20%20%20%20FilterCurrent%20%3D%20Table.SelectRows(Source%2C%20each%20(%5BFilter2%5D%20%3D%20true))%2C%0A%20%20%20%20RemoveFilters%20%3D%20Table.RemoveColumns(FilterCurrent%2C%7B%20%22dataMonth%22%2C%20%22Filter1%22%2C%20%22Filter2%22%7D)%0Ain%0A%20%20%20%20RemoveFilters%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFuture%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20thisMonth%20%3D%20Date.StartOfMonth(%20Date.From(%20DateTime.LocalNow()%20)%20)%2C%0A%20%20%20%20thisMonthNumber%20%3D%20Date.Month(%20thisMonth%20)%2C%0A%20%20%20%20otherMonths%20%3D%20%7BthisMonthNumber%2B1%20..%2012%7D%2C%0A%20%20%20%20Source%20%3D%20Data%2C%0A%20%20%20%20FilterCurrent%20%3D%20Table.SelectRows(Source%2C%20each%20(%5BFilter2%5D%20%3D%20true))%2C%0A%20%20%20%20RemoveFilters%20%3D%20Table.RemoveColumns(FilterCurrent%2C%7B%22Month%22%2C%20%22dataMonth%22%2C%20%22Filter1%22%2C%20%22Filter2%22%7D)%2C%0A%20%20%20%20AddMonths%20%3D%20Table.AddColumn(RemoveFilters%2C%20%22Month%22%2C%20each%20otherMonths)%2C%0A%20%20%20%20Duplicate%20%3D%20Table.ExpandListColumn(AddMonths%2C%20%22Month%22)%0Ain%0A%20%20%20%20Duplicate%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EfullYear%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Table.Combine(%7BPrevious%2C%20Current%2C%20Future%7D)%0Ain%0A%20%20%20%20Source%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2804355%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%3A%20Duplicate%20Rows%2C%20MONTH(TODAY())%2C%20and%20Add%20to%20Month%20For%20Duplicated%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804355%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F830570%22%20target%3D%22_blank%22%3E%40Jpalaci1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOops%2C%20forgot%20to%20check%20if%20we%20are%20in%20December%20duplication%20is%20not%20required.%20As%20variant%20you%20may%20add%20if%20...%20then%20...%20else.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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?

 

7 Replies

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

@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
best response confirmed by Jpalaci1 (Contributor)
Solution

@Jpalaci1 

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

Thank you for the help. This helped!
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