Hi Mynda,
I want to know the best way to change my cell values in my "Revenue" column (as they are more of a contra asset account) using power query, in a dynamic enough way for when bring the new data for each month. Right now my data has more or less the following columns month/account/description/revenue/expenses/COGS.
I have a uploaded a quick sample data and I highlighted the data yellow that needs to be negative.
Thanks,
Michael P.
Hi Michael,
You can check the Description column for words that indicate the value in the Revenue column should be negative, e.g. look for "Discounts" or "Allowance".
If you find one of those words then multiply the value by -1.
See attached file.
Regards
Phil
Hi Philip,
Thanks for the quick reply. I agree but I would rather do it where the account is ##, that revenue cell should be multiplied by -1. However I don't know the M language to do that.
Thanks,
Michael P.
I took Phil's file and added a table listing the negative revenue accounts and used that to identify the revenue numbers to be changed. All was done by clicking in the user interface and adding a straight-forward custom column. No complicated M-coding needed.
Very helpful. Thank you both.
