Excel 2013 PowerPivot & Advanced Business Intelligence Tools

Explore methods to import external data into Excel, from Access, SQL Server, XML, text files, and web sources, using Get External Data and Power Pivot for analysis.

Explore how relational databases store data in separate tables with primary and foreign keys, and learn to join tables through queries to extract insights from orders, customers, and products.

Explore OLAP's fast, pre-processed analysis with dimensions and facts in a star schema, illustrating hierarchies and pivot table drilling in Excel.

Discover how queries join related tables via primary and foreign keys, apply filters and sorts, and build pivot tables from the results using GUI tools or SQL.

Import access data to create a pivot table from multiple related tables in Excel 2013. Use the from Access button to import from a database or saved queries.

Learn how to import web data into Excel using the web query tool, identify tables with yellow arrows, and manage pages with junk data or no tables.

Learn how to use Microsoft Query to import data into Excel by creating a data source with an ODBC driver, selecting Access or SQL Server, and returning results to Excel.

Learn to build targeted data queries in Microsoft Query for the Northwind database, using the wizard or graphical interface to return data to Excel and create pivot tables.

Learn how to base a pivot table on external data using the create pivot table dialog, manage existing connections, and browse for new data sources with data connection wizard options.

Explore the Excel data model and the power pivot add-in in Excel 2013, including availability, differences, and how to build pivot tables from unrelatable or related tables.

Explore how the Excel 2013 data model, part of power pivot, enables multi-table relationships, large in-memory data, and named sets to enhance pivot tables beyond regular Excel.

Relate multiple data sources in the Excel data model to pivot tables. Use primary and foreign keys to establish relationships and add slicers for analysis with or without Power Pivot.

Explore how the Excel data model enables compact pivot tables by importing sources and creating relationships, and why the Power Pivot add-in enhances with DAX for calculated columns and fields.

Migrate from PowerPivot 2010 to Excel 2013 by upgrading the file's data model once, adapting to the 2013 PowerPivot window, and noting Power View compatibility and measures behavior.

Explore power pivot core features and the daks language using the built-in Excel 2013 help, tutorials, and videos to start quickly and deepen your understanding.

Navigate the Power Pivot user interface in Excel 2013, exploring the Power Pivot tab, manage window, data and diagram views, calculated fields, and data model relationships for efficient analysis.

Import azure datamarket data into PowerPivot to enrich analyses with calendar and weather tables. Sign in, subscribe, and import tables, linking date keys to a calendar.

Learn three methods to load data into the power pivot data model—copy and paste, linked table, and importing from another Excel file—and how updates reflect, enabling pivot table creation.

Create relationships in PowerPivot by linking tables through primary and foreign keys within Excel, import data from Access, Excel, and text files, and manage relationships to enable accurate pivot tables.

Build power pivot tables from a three-source data model, using the fields pane, slicers, and distinct count to analyze sales by year, month, continent, and region.

Explore additional pivot table and power pivot options in Excel 2013, including pivot chart combinations, slicer report connections, and the flattened tabular layout for quick, independent chart and table configurations.

Learn how slicers work with power pivot in Excel 2013, including vertical and horizontal alignment, moving and resizing, and linking slicers to multiple pivot tables via report connections.

View imported data in power pivot's data view to sort, filter, hide columns, and use dacs to create calculated columns and fields.

DAX expressions power calculations in Power Pivot, creating calculated columns and calculated fields (measures) to compute net revenue within a data model for pivot tables.

Explore DAX functions in Power Pivot, using IntelliSense to build calculated columns and tables, and consult DAX reference help to learn arguments, return values, and nesting.

Master creating calculated columns with DAX in Power Pivot, using table and column references, filling entire columns, and applying date, text, and logical functions to enhance pivot table reports.

Explore how DAX related and relatedtable functions pull data across many-to-one relationships, create calculated columns such as margin, sum return amounts with sumx, and handle upstream-downstream constraints and many-to-many pitfalls.

Evaluate whether to denormalize an Excel data model for Power Pivot, balancing normalizing and flattening with related tables and lookups for pivot table clarity.

Use the query designer to denormalize data into a single table. Relate dates, stores, and geography, add calculated columns, and streamline the data model for pivot tables.

Discover how DAX calculated fields power Power Pivot by enabling on-demand calculations in the data model, offering explicit and implicit fields and improving performance over traditional pivot table calculations.

Master DAX calculated fields to define revenue (subtract returns) and cost (exclude overhead) alongside profit in Power Pivot, build ratios, and automatically update dependent fields for faster, accurate company reports.

Explore how pivot tables compute revenue, products sold, and revenue per product by tracing table relationships and filters from geography and product slicers to the sales data.

Learn how many-to-many relationships challenge Power Pivot tables when linking a sales table to products and geography, and fix it with a distinct count calculated field in the sales table.

Learn how to count distinct values in PowerPivot pivot tables using distinct count and DAX calculated fields, with practical examples like distinct products sold by state.

Master the DAX calculate function to build powerful calculated fields and pivot tables with filter context in PowerPivot. Explore how filters and slicers shape results across product categories and geography.

Master advanced calculate techniques in PowerPivot to compute total sales and the percent of U.S. sales across geography fields, using slicers and the ALL function to manage pivot table filters.

Learn to use PowerPivot date functions to create year-to-date and running totals in pivot tables, using a calendar date table and calculated fields like total sales and total sales YTD.

Add a parameter table to Power Pivot data model for tax-rate what-if analysis, use a slicer to select one rate, and build a tax calculated field with hasonevalue and values.

Create hierarchies in power pivot for date, store, and product dimensions to enable drill-down in pivot tables, using diagram view, the dates table, and the Y Q M order.

Explore named sets in power pivot to predefine country filters for pivot tables. Create and manage these sets with the set manager, and adjust totals to show only filtered items.

Explore creating key performance indicators (KPIs) in PowerPivot, using base and target fields, DAX calculations, and visual indicators to compare net revenue against targets.

Explore cube functions in power pivot, linking pivot tables to OLAP cubes and MDX; convert pivot tables to cube formulas using cube member and cube value for custom reports.

Discover how Power View, an Excel 2013 COM add-in, works with Power Pivot to build insightful reports from an Excel data model, using tables, charts, or maps.

Explore how Power View uses the Excel data model and Power Pivot to create reports, with calculated columns, fields, and hierarchies that keep tables visible or hidden.

Explore power view to create flexible reports from your data model, using tables, charts, matrices, and maps with filters and slicers.

Consolidate your power pivot and advanced BI skills through hands-on practice and experimentation. Continue learning by consulting online help and reference books to stay current with Excel 2013 BI tools.