Forum Discussion
Duplicate rows based on criterias
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
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.
3 Replies
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.
=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))
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.
