Duplicate rows based on criterias

[Thiago_Font]

Hi, I've searched the forum and didn't find any solution that looks like with my issue.

I have a data set of accruals by month, and the amount needs to be reversed in the following month i.e:

Name	Mov.TP	Month	Amount
Freight	Accrual	1	100
Freight	Revesal	2	-100

What I'm trying to do is the following

"if mov.TP"="Accrual" then Month +1 and Amount *(-1) and Mov.TP="Reversal".

I tried to merge the columns into rows and created some calculated columns but the number of rows grew exponentially due to the nuumber of columns in the table.

Thanks in advance for any help

[Anonymous · Accepted solution]

Hi @Thiago_Font ,

You can add three custom columns in Power Query Editor.

You can download my attachment for more details.

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.

[wdx223_Daniel]

=Table.FromRecords(List.TransformMany(Table.ToRecords(PreviousStepName),each if [#"Mov.TP"]="Accural" then {_,_&[#"Mov.TP"="Reversal",Month=[Month]+1,Amount=[Amount]*-1]} else {_},(x,y)=>y))

[HotChilli]

I think you could do this by duplicating the table, filter to keep only the "Accrual" rows and add custom columns for the values required (Month, amount, mov.tp ) - these columns will need a different name but you can create them, remove the original columns and then rename to get what you want).

You can then append this table with the original table.