Excel and Power Query/Power Pivot Reporting

cmcreynolds

Active Member
Joined
May 21, 2015
Messages
295
Hello everyone - just needing advice here.

I have been tasked to create a 'simple' four-page report on a quarterly basis. Each page will summarize data coming from a different data source (Pg 1 is via MS Access querying our Customer Management Software's database, Pg 2 is a spreadsheet from our email server, Pg. 4 is from a different department's Access database, etc.).

My initial thought is to save monthly data in a folder grouped by the page (so the data structures of each spreadsheet are the same), then with Power Query, get the data into the Data Model and summarize each page with its own Power Pivot Table.


But, I have two questions that arise:

1. I would like to export all four of my pages as one PDF. So, can I put the pivot tables all on one worksheet? The number of rows in each pivot table won't change (I'll be summarizing by our membership types). Or is there a way in Excel to put different worksheets together and export as a PDF?

2. Since each page/pivot table will be summarizing data from different sources, can I still have a "master slicer" (by Fiscal Quarters on a separate worksheet, perhaps?) that controls the different date fields? (Do I just make sure they're all related to the same calendar table?)

3. Should I even be doing this in Excel? Should I trying querying in MS Access 2013?

Thanks again for your help, I'm just trying my best to create this report and make it work easily for others in my organization.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Your approach is fine. I suggest just 1 Excel Power Pivot workbook with all data from many sources in the one data model. Depending on the differences in data, you will have between 1 and 4 data tables. Join each data table to common lookup tables.

Multiple Data Tables in Power Pivot - Excelerator BI

1. Given you will have one workbook, you can save as PDF all 4 pages together.
2. Yes, read the link to the article I wrote above.
3. Yes Excel - definitely not Access. You could also consider Power BI What is Power BI - Excelerator BI
 
Upvote 0
Great, Thank you, Matt.

My new company isn't ready for anything Power BI -related (although dashboards would be nice!)

Since I'm gathering all this data into Excel, should I bring in individual records, or bring in summaries by date? I foresee monthly pulls to be maybe 2.5k rows each, so I don't think file size will get astronomical even after a year.
 
Last edited:
Upvote 0
My default answer is "bring in the detail if size is not an issue". You can't drill down and analyse what isn't there. 2.5k is micro. When you get to a total of 1million rows (depending on columns) then if you have 32 bit you can start to be concerned. If you have 64 bit, you will never hit an issue.
 
Upvote 0
Great, Thank you, Matt.

My new company isn't ready for anything Power BI -related (although dashboards would be nice!)

Since I'm gathering all this data into Excel, should I bring in individual records, or bring in summaries by date? I foresee monthly pulls to be maybe 2.5k rows each, so I don't think file size will get astronomical even after a year.

Rather, should I aggregate then put into Excel, or aggregate with Power Query/Power Pivot?
 
Upvote 0
I don't understand the question, but let me try to answer what I think you need to know. You should bring in the detail data into Power Pivot. You can do that using Power Query if you like or simply bring it directly into Power Pivot. Either way you want the line level detail in Power Pivot. Note that bringing in the detail doesn't mean you should replicate the exact shape of your data in Power Pivot.

Read this article. It covers many things (including a few relevant points to this topic) Best Practices for Power Pivot, Power Query and Power BI - Excelerator BI
 
Upvote 0
I think I overlapped your responses, sorry. You answered my question about bringing in the detail. RIGHT NOW, this company just wants PDFs so they can print them out, but I am not sure they understand the utilization of being able to drill down.

I believe I'm going to bring in the detail (you're right - 2.5k is NOT going to be an issue in this report) - this way, my Excel report has the capability of being drilled down into, and once I convince the higher ups that they can and should :) do that, it will already be in place.

Thanks again for the advice.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top