• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Data from Report Pulling into Data Model|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Data from Report Pulling into Data Model|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotData from Report Pulling into Data …
sp_PrintTopic sp_TopicIcon
Data from Report Pulling into Data Model
Avatar
Clay Vogt

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
June 29, 2021
sp_UserOfflineSmall Offline
1
July 6, 2021 - 6:01 am
sp_Permalink sp_Print

Good Afternoon!  I feel a little "needy" posting another question here so soon, but here goes:

Now that I'm learning how useful Power Query and Power Pivot are, I'm trying to automate a lot of my department's monthly reports and KPIs.  I'm having some trouble with the attached report.  This report takes a valuation of every inventory item we have in stock across three plants (only two have been uploaded to the report so far:  GLA & GSA) in the 'Inventory' Tab and compares the customer demand we have on items in our inventory from the 'Demand Tab'.  I will be pulling these in from a folder where I upload the inventory and demand tables each month.

I then have to cross-reference the reports to get a total list of items in our inventory that are No-Use (have zero demand and will therefore be unused) and what the total No-Use cost is at each plant.  This has been combined and calculated on the 'Report Data' Tab (No more VLOOKUP month-after-month, YAY!).  I then present the report to my team and each buyer has to respectively categorize where items in their account fall within specified categories and then list the action they'll be taking to eliminate the unnecessary inventory.  After each item is categorized, I then have to take the categorizations and formulate a pivot table for each plant showing how many NU dollars are held in each category at the respective plant.

I've validated the category column so that the buyer can select the appropriate category for each item from a drop-down list of applicable categories.  The problem I'm having is uploading the buyer input into the data model (PowerPivot), so I can then use the category they've selected for each item and create the pivot tables that I need to for presentation to my corporate team.  I've run testing and the way the connection are set now, anytime I select various categories and go into PowerPivot and refresh the data that column just clears any entries out because I created that column in PowerPivot.  How can I set up the Data Model so that I can combine the two reports into the "Report Data" tab that I need to run, then give it to my team where they manipulate the data in the "Category" column, but still keep their entries to complete the next step of running the measure's and pivot tables on the data they've changed in the report?

I've also attached a screenshot where I think I may have found my answer as I was typing this:  If I chose one of the highlighted bubbles in the properties pop-up, will this allow their entries to upload into the model?  The other idea I had was to create a new data source from this table, that's uploaded into the model, but I wasn't sure if I could create a 2nd data source from a table that was outputted from one of my reference queries.

Thanks in advance helping with this!NU-Data-Properites.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage NU-Data-Properites.jpg (87 KB)
Avatar
Clay Vogt

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
June 29, 2021
sp_UserOfflineSmall Offline
2
July 6, 2021 - 6:44 am
sp_Permalink sp_Print

Okay--

I think I found something out, I'm just not sure what.  If I input some categories and then click "Add to Model", it takes the table and adds a 2nd tab to the PowerPivot model.  Pictures below.  The categories then appear on the 2nd model tab.  But, when I refresh all, the categorizations disappear form the table. My KPI measures for each categegory do work in my 2nd tab/model.

How though, do I get this to just update the first model and keep everything on one tab and report rather than creating a whole 2nd tab that reports the same exact information... as well as the categories disappearing (I often need to go back and discuss and re-categorize with the buyer, depending on if dollar amounts in that category went up/down from one month to the last).   I need to keep this all on the same tab/table for visibility, as well as ideally, keeping all the measures I've circled, all on one tab/model.Add-to-Model.jpgImage Enlarger

2nd-Tab-in-Model.jpgImage Enlarger
1st-Tab-in-Model-after-Categorization-Tab-added-to-model.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Add-to-Model.jpg (86 KB)
  • sp_PlupImage 2nd-Tab-in-Model.jpg (88 KB)
  • sp_PlupImage 1st-Tab-in-Model-after-Categorization-Tab-added-to-model.jpg (100 KB)
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Bhuwan Devkota
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31909
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.