Log In
Register
Home
June 19, 2021
Offline
Hello. I am looking to revamp billing reports I created a LONG time ago, and I’m considering using tables and pivot tables to do that, but I have a question before I start working on it.
Currently our billing reports are separated by months within a fiscal year, July through June. Within each month’s reports, the data is broken up by week. I know that you can adjust the group filter by days starting on a certain date and then adding 7 days to that date, HOWEVER, that is only going to cause issues for us, I think. Our weeks start on Mondays, but if the first of the month falls on a Thursday, for example, then that Thursday through Sunday would be week one, and the subsequent Monday would start week two. Is it possible to group the data in this way using pivot tables? If not, then how can I do that? Keep in mind that I’m creating the reports and someone else a number of states away who is not very well versed in Excel will be doing the data entry and final reporting. Any input will be greatly appreciated!
Thanks in advance!
Moderators
January 31, 2022
Online
Difficult to judge. When you say "billing reports" are these summarised reports by month/week or do these also include the individual invoice records?
Ideally, you would capture all invoices in one large table. No empty rows or columns, no fancy formatting. Just raw data. And then you can use formulas to calculate week-numbers from the invoice dates. And then use pivot tables to summarise the data.
But, I wonder what the added value of weekly summaries are when one week in a month covers only 4 days where others cover 7 days. Such a comparison would be meaningless.
Perhaps you can upload a file with a representative example of the data you are working with and the reports generated from it.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
If you add the week number to the data table then I see no problem to group by week number. See attached file for an example. If you don't want such helper column in your data table, it can be added using Power Query.
Br,
Anders
June 19, 2021
Offline
Okay. Thanks much for all the input. As requested, attached is a PDF of a portion of one of the documents we have to submit for billing to who we are contracted to. The reports are submitted monthly, and they are to be broken up by week, and then by each day within that week. Also, this does not include any invoices as such, just passenger counts on a daily basis that are monitored for accurate reporting up the ladder to the Federal Government, which effects subsidized funding for the service. I hope that the attachment helps clarify what I am looking at.
Anders, how would Power Query handle that? Is that something I could set up on my end, and then it be automatic for the person doing the data entry and final report submission, that way they do not have to deal with the Power Query? And, would they have to change anything within their Excel settings to handle Power Query, or even Power Pivots, etc.?
Thanks again. Look forward to hearing any input on this issue.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Power Query lets you import data from different sources, gives you tools to transform the data so it suits your needs and then you present it what ways you want. Do you use old Excel version then perhaps you don't have it, check out this old blog post Power Query Version Compatibility and Installation.
I can also recommend you to read the blog article Power Query Date Formats • My Online Training Hub.
Br,
Anders
June 19, 2021
Offline
Thank you. That is very helpful! I know I can run Power Query in my version of Excel, but if I create files using Power Query and/or Power Pivot, can the person doing the data entry have to do anything on their end to run the reports? Or would it automatically function as I set it up to do with the other person only doing data entry? Haven’t gotten any clarification on that from anywhere.
Thanks again!
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Assuming that the source data that is loaded into Power Query in this case will be a table in the same workbook, then it will work for everyone having access to the workbook that uses Excel version having Power Query. It all depends on where the source is and if the user has access to the source. Also, it depends on what Excel version you have if you are able to update the source (connectors).
After more data is entered to the table, the user would then need to refresh the query, either manually from the Data ribbon and Refresh/Refresh all button or using the keyboard shortcuts. You can of course set that the query auto refreshes and so on.
But, assuming again, if you want to use Power Query only because you don't want to add or have visible such helper columns to the input table, then consider hiding those helper columns. With Power Query you need to refresh the new data, the same with Pivot Table.
My setup would be one table where new data is entered, in another sheet I would create the report view (as in your attached PDF) and use formulas to get the needed data combined with slicer to filter the report to show selected year/month. If I want to add charts, or if there is a need to quickly change or add grouping, then I would use a Pivot Table.
Br,
Anders
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Adding a simple example file based on your PDF as source. When testing you can add new data to the Page001 table and then refresh the Pivot Table and the added data is showing, but if you refresh the Power Query table the added data will be removed as it does not exist in the source data. But you need of course to refresh the Pivot Table again to get rid of the removed data.
I hope this help you in deciding best options forward.
Br,
Anders
1 Guest(s)
