Hello,
I Have a table
I need to create a row for each bolded row value such that I get contract month for the prior month.
this is how the end result should look like
I tried to use the following Power Query code to do this
= let
createPriorMonthRow = (table as table) =>
let
currentDate = DateTime.LocalNow(),
currentMonth = Date.Month(currentDate),
priorMonth = Date.AddMonths(currentDate, -1),
priorMonthValue = Date.StartOfMonth(priorMonth),
filteredRows = Table.SelectRows(table, each Date.Month([Contract Month]) = currentMonth and [Custom] = "Yes"),
duplicatedRows = Table.FromRecords(List.Transform(Table.ToRecords(filteredRows), each Record.FromList(Record.ToList(_) & {priorMonthValue}, Table.ColumnNames(table) & {"Contract Month"}))),
updatedTable = Table.Combine({table, duplicatedRows})
in
updatedTable,
// Example usage#(cr)#(lf)
source = SampleTest,
updatedTable = createPriorMonthRow
in
updatedTable
but it gvies me an error saying:
Expression.Error: Expected a TransformOperations value. Details: [List]
I am not really well versed with Power Query M COde, so not understanding where i am going wrong
Any help would be appreciated.
Thanks
