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.

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.

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.

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.

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.

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.

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 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 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.

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.