Results 1 to 7 of 7

Thread: Excel and Power Query/Power Pivot Reporting
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2015
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel and Power Query/Power Pivot Reporting

    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.

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel and Power Query/Power Pivot Reporting

    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
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    Board Regular
    Join Date
    May 2015
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel and Power Query/Power Pivot Reporting

    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 by cmcreynolds; Feb 2nd, 2017 at 10:29 AM.

  4. #4
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel and Power Query/Power Pivot Reporting

    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.
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  5. #5
    Board Regular
    Join Date
    May 2015
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel and Power Query/Power Pivot Reporting

    Quote Originally Posted by cmcreynolds View Post
    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?

  6. #6
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel and Power Query/Power Pivot Reporting

    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
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  7. #7
    Board Regular
    Join Date
    May 2015
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel and Power Query/Power Pivot Reporting

    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 by cmcreynolds; Feb 3rd, 2017 at 08:10 AM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •