Forum Discussion
VoIP billing in Excel
Hi All, I am trying to create a spreadsheet to calculate my client's VoIP call cost. Unfortunately, all clients are on one excel CSV spreadsheet, so I want it to separate the one spreadsheet into multiple under the client number and then I want it to recognize the service provider and determine which rate to use. Test spreadsheet attached, can someone help?
Janedb Okay, then you need to do some studying first. A good starting point could be here:
https://exceloffthegrid.com/power-query-introduction/ , and there are many more resources on-line. Just Google and pick a few.
Don't expect it to be super easy from the start, but as said, once you have started using PQ, you will find it difficult to not use for almost everything you do in Excel.
6 Replies
Janedb This looks like a job for Power Query (PQ). In the attached file, I transformed your data to a format you can work further with in PQ or outside of PQ. For instance, attaching the correct rates per provider, calculate the cost and summarise everything by client in a pivot table, for instance.
Columns K to Q in the attached file contain the output of very basic PQ steps.
Are you familiar with PQ? If not, it's going to take a bit getting used to, but once you get the hang of it you'll love it. In a real life scenario you would connect to the CSV file without loading it into Excel first.
- Hi Riny, I have never worked on PQ yet. How do I link the csv?
Janedb Okay, then you need to do some studying first. A good starting point could be here:
https://exceloffthegrid.com/power-query-introduction/ , and there are many more resources on-line. Just Google and pick a few.
Don't expect it to be super easy from the start, but as said, once you have started using PQ, you will find it difficult to not use for almost everything you do in Excel.
