undefined

[Anonymous]

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

[Anonymous]

Hi @Anonymous ,

Below is the method to get value from previsou row in Power Query. Please refer to

Value from previous row – Power Query, M language – Trainings, consultancy, tutorials (exceltown.com)

You need to set a grouping as a column for your bold font row. Bold characters are not recognized in Power Query as a basis for grouping.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.