+ Reply to Thread
Results 1 to 4 of 4

Power Query Help to create manufacturing company dashboard from two reports

  1. #1
    Registered User
    Join Date
    02-18-2019
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    11

    Power Query Help to create manufacturing company dashboard from two reports

    Good Afternoon,

    I am attempting to create a dashboard that will allow me to simply extract two individual reports from my companies content management system, copy and paste their data sets into tables within a workbook, refresh all data, and populate a nice dashboard with different charts based on pivot tables. I have a general understanding of how to do this manually, but refining large data sets over and over gets old. Unfortunately I cannot share data so I will do my best to explain my current steps.

    First off, to make things easy we manufacture a product for sales. Products unique names, and unique codes.

    The reports I extract from our content management system is a review of production, quality control, etc.... I can export an individual day or any range of dates/years as preferred. Sometimes ill grab a full year.

    Report shows me;
    Production Date
    Product Code
    Product Name
    QC's results etc

    I'm using a query from the "data input" tab to transform and add some columns that did not exist in the original data set to a new sheet named "Summary". I format the "refined" data as a table in the sheet called Summary, which then I turn into pivot tables and charts on the dashboard tab.

    Where I am really struggling is;

    1.) I added macros to clear the "input" tables and delete all rows except headers, and one additional blank row that I can copy my new data into.
    2.) I recorded a macro that was essentially just me clicking refresh all (data) and added it to a button like the clear macros.

    Now, when I clear the input table/query table and extract a new date range of data from our CMS, copy and paste it into the "data input" tab/table, go to the "Summary" tab, click the refresh macro, it pulls the former date ranges and not the new ones that i just copied and pasted into input table.

    TLDR:

    I want to create a report with an "Input" tab that is just a copy and paste of "fresh" data. Clean/add/refine through query using refresh macro to summary tab, formats as a table, which then i can push pivot tables through.

    Any ideas would be greatly appreciated. I have gotten very close, but kind of going into this blind. The one I though I had just the way i wanted it the data connection broke the next morning when i opened the file.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query Help to create manufacturing company dashboard from two reports

    It's really hard to answer this without seeing your file / code.

    General principles:

    1. Use Power Query to get data directly from your "extracted" source data file (eliminate the copy paste step).
    2. Load to the data model, and base your pivot table reports on the data model, instead of materialising your query into a table.

    These two steps will remove the need to "clear" tables.

    If this doesn't steer you clearly enough, then create a sample workbook with suitably anonymised data, so that we can see your structure / queries / code.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    02-18-2019
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Power Query Help to create manufacturing company dashboard from two reports

    Hey Olly,

    This would be great, our content management system is protected with username and password. I have looked extensively online on this subject but ASPnet is restricting me from being able to do this, or I believe it is. To get around this, I extract data from our software, copy and paste it into a "raw data workbook" and run my query through that. Its not the most efficient but with limited resources I am doing the best I can to make report generating as easy for me as I can.

    After I posted this I successfully built my query as I explained and everything works 100% as it should. The issue I have no is;

    I am using multiple pivot tables from the query data that is pulled from my raw data sheet. These pivot tables are turned into charts in a tab i call graphics. I use two slicers to filter all of the pivot table data. One uses the first letter of the product name to filter out the other slicer. The only issue is when I use this slicer, my charts become blank. When I then click the slicer with the product name it changes my chart type to a cluster column rather than a combo. I understand it is doing this because the data "clears" when I use the slicer to search the first letter. I dont want to go through all of my slicer filters and save the chart template.... Ive spent alot of time building this report and it takes the wind out of my sails when excel has known bugs for years with no fix ever applied. I have read about backdooring data from pivot tables but how my data is set up I dont know how to make that work.

    Thank you for your time!

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query Help to create manufacturing company dashboard from two reports

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 23
    Last Post: 12-15-2022, 05:14 AM
  2. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  3. [SOLVED] Best way to set up data for visual reports (dashboard)
    By dougers1 in forum Excel General
    Replies: 8
    Last Post: 02-13-2019, 03:36 PM
  4. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  5. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  6. Power Map window on dashboard
    By jwillis07 in forum Excel General
    Replies: 0
    Last Post: 08-06-2015, 07:52 AM
  7. [SOLVED] graph add ins for dials or dashboard reports?
    By BhewesPSU91 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-28-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1