Publication number: ELQ-40005-1
View all versions & Certificate
Structure and analyse Data with Power Query and Power Pivot
Power Query and Power Pivot integrated solution for consolidation and analysis of input data.
This Best Practice shows how to use Power Query and Power Pivot to first consolidate data into a useful data model, create data integrity and then analyse with DAX formulas instead of extended spreadsheets.
The Practice also demonstrates how to use slicers and timelines to select portions and choose time periods on a chosen data model with consolidated data to answer detailed questions.
It starts using Power Query to clean,, consolidate and structure input data (here from tables within the same file) and then uses Power Pivot to create a Data Model, extend this with some useful columns, before relationships can be set up.
The columns as well as the measures to answer specific questions use DAX code, which has some comments for better understanding and which I tried to keep short.
DAX is functional programming, at the end same as Excel formulas, where formulas can be cascated and nested within each other, as.defined measures can reference other measures as long there is no recursive or circular use of measures or column functions.
The provided best case uses Stage 6 Case "Soccer Stats" (https://www.eloquens.com/tool/Nb0NtP3g/finance/financial-modelling-world-cup-best-practices/stage-6-2021-soccer-stats) from Financial Modelling Worldcup and provides a solution that only uses Power Query and Power Pivot for solution to answer questions. For Case Materials, Case Description, Questions, and other solutions refer to the Best Practice published by FMWC.
This Best Practice includes
1 Excel File with original cover and input data, the solution with DAX codes and answers to questions
Acquire business license for $15.00
Add to cart
Add to bookmarks
Alternative to straight Excel solution
Office 365 Installation of MS Excel
- Be the first to review this Downloadable Best Practice
Write a review
People using this Best Practice also downloaded
2015 Round 1: TaxExcel training/competition model from the 2015 Financial Modeling World Championshipsfinancial modelingexcel skillsfinancial modeling skillsfinancial modeling trainingworld championships168add_shopping_cart
Telecoms Financial ModelTelecom Financial Model presents the case of an already operating business in the telecom sector (mobile communications,financial modelfinancial modelinginternetexcelmobile728add_shopping_cart$149.00by Big4WallStreet
Author Case Study: Lance RubinDiscover the story of Eloquens Author Lance Rubin and how Eloquens.com has been useful for him.case studyfinancial modelingmarketplacefinancial modelsauthor152freeby Eloquens Academy
2015 Round 2: The Price Is RightExcel training/competition model from the 2015 Financial Modeling World Championshipsexpensesfinancial modelingcash flow statementexcel skillsexcel training209add_shopping_cart
2015 Round 1: Bread and ButterExcel training/competition model from the 2015 Financial Modeling World Championshipsp&lirrbalance sheetfinancial modelingcash flow statement696add_shopping_cart
DCF Model of a Pharma CompanyThis is the Discounted Cash Flow Model of DR. Reddy's Laboratories (India) prepared in October 2020dcffinancial modelingdiscounted cash flowpharmaceutical company405add_shopping_cart$20.00by Shubham Patil
To Model or not to ModelA REFERENCE SHEET on why and how Finance Models are constructedone pagefinancial modelingwhyfinancial modelsto model or not to model356add_shopping_cartfreeby David Hubbard
How to make Financial Models Easier to Understand, Use and Audit (5 tips)A guide with 5 tips to build Financial Models easy to Understand, Use and Audit.guidebest practicetipsfinancial modelingfinancial modelling352add_shopping_cartfreeby Matthew Bernath
eCommerce Startup Valuation: Financial Modelling TutorialTutorial on building a financial model for an e-commerce startup valuation.valuationfinancial modelstartupfinancial modelingecommerce1,453freeby CFI
2013 Round 1: Acquisition FinancingExcel training/competition modeling challenge from the 2013 Financial Modeling World Championshipsacquisitiondebtfinancial modelingm&abonds273add_shopping_cart
Any questions on Structure And Analyse Data With Power Query And Power Pivot?
The user community and author are here to help. Go ahead!