Hi there,
Attached I have included a P&L Report. I need to get the data into a suitable format for a table First I need to edit all Expenses to a -ve format. Is there any way this can be done in a bulk mode rather than individually
I look forward to your reply
Not sure what you are asking. It would be real helpful if you mocked up a solution showing what it is your expected results should look like.
As the data is not in a perfect format, it needs editing before loading into Power Query..and finally into a Table Format
Cheers
Chris
Your file has only one visible sheet "AprWorkings", and one hidden called "Apr". May I assume that the latter is the Xero report?
And may I assume that you want to turn that into one table where Income = positive and Expense = negative. If not, please explain your comment "edit all Expenses to a -ve format".
By the way, the Apr sheet (hidden) have very odd formulas for the sub-totals. Is that how Xero does that?
Anyhow, you need what I think, please have a look at the attached file in the Apr sheet. You'll find a named range called Xero_data and a PQ loaded table based on that range. The applied steps are very basic and easy to follow.
Thanks very much for that help Riny. Very Helpful
How did you apply "-" to all the expenses in a bulk process?
Cheers
Chris
Well, I didn't really. After unpivoting the data I added a custom column saying that, on a row-by-row basis, "if the Category column equals "Operating Expenses" then multiply the Value by -1 else just take the Value as it is. Then remove the original Value column and and rename the one just created. It's quite crude, but easy to follow and maintain.