Forum Discussion
Jpalaci1
Oct 01, 2021Brass Contributor
Power Query: Duplicate Rows, MONTH(TODAY()), and Add to Month For Duplicated Rows
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 ...
- Oct 01, 2021
Oops, forgot to check if we are in December duplication is not required. As variant you may add if ... then ... else.
SergeiBaklan
Oct 01, 2021Diamond Contributor
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
- Jpalaci1Oct 06, 2021Brass ContributorThank you so much!
- SergeiBaklanOct 06, 2021Diamond Contributor
Jpalaci1 , you are welcome