Excel BI: Power Query and Power Pivot

Objectives

To learn how to use the new data acquisition and modeling capabilities in Excel.

Targeted audience

Professionals and technicians

Content

Getting Data with Power Query

• Importing data: from the Web, a file, a database, or a Web service
• Adjusting data in the query editor
• Keeping or removing rows and/or columns
• Transforming data
• Filtering data
• Splitting and renaming a column
• Unpivoting data
• Inserting custom columns
• Adding or duplicating a query
• Combining queries: append and merge
• Creating parameters and using them in a query

Modeling Data with Power Pivot

• Excel data models
• Adding data to the model: Excel tables, Power Query queries, Excel files, databases
• Defining relationships between tables
• Managing the model and creating relationships in the diagram view
• Building a hierarchy (geographical, temporal, business)
• Cleaning the data model

Extending the Data Model with DAX

• Adding calculated columns to the model
• Overview of the DAX (Data Analysis Expressions) language for creating calculated columns or measures with formulas
• Creating measures
• Adding a linked column
• Creating a key performance indicator

Visualizing Data

• Demonstration of creating reports with Power BI Desktop