• Originally published: 12/07/2021 14:17
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.

Description
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

Discuss

## Further information

### Objectives

Alternative to straight Excel solution

### Use it if

Office 365 Installation of MS Excel

## Reviews

• Be the first to review this Downloadable Best Practice

See all

## Any questions on Structure And Analyse Data With Power Query And Power Pivot?

The user community and author are here to help. Go ahead!

0.0 / 5 (0 votes)

• -
• -
• -
• -
• -