Give your data meaning. Analyze huge data sets (millions of rows) with speed in with Excel’s Power Pivot

Probably you spend too much time doing lookups of data from various tables for use in a pivot table, uh! Would you want to handle millions of rows of data (say 100 million plus, but am sure you deal with a fraction of that) in a familiar environment –Excel? Would you like to automate that manual process of reshaping your data and unifying it –all with no single line of code? How about being able to monitor your regional/branch/department budgets versus actuals all in one interactive Pivot report?

Look no further for this course just addresses this and much more.

Each month/week/day you just need to update your source data, and press Refresh All –no formula re-work!

In this module you will learn:

  1. Importing and shaping your data with Power Query –automated with no VBA!
  2. Creating relationship between different source tables
  3. Supercharged calculations with Data Analysis Expressions (DAX) –extend the capability of Excel
  4. Interactive reports and dashboards –Power Pivot report tables, charts and slicers

The learner is assumed to be familiar with lookup formulas, Pivot tables and Pivot charts in Excel.

Versions & compatibility:

Not all version of Excel include Power Pivot. If you have Excel 2013 or 2016 Professional Plus edition, PowerPivot is already built-in and all you need to do is enable it from File >>>Options >>>Ad-ins >>>COM add-ins. For Excel 2010, Power Pivot is available for free download and installation.

Power Query is a free download add-in for Excel 2013 and 2010. With Excel 2016, it is in-built, and is available in the Data menu >>>Get & Transform.

I recommend if you could install MS Office 2016 Professional Plus.

Can others access my reports?

Yes, anyone with any version of Excel will be able to view and slice the reports generated by PowerPivot, even if they don’t have it installed. However, they won’t be able to make major structural changes unless they install.

  • The Microsoft Power Series –Power Pivot, Power Query, & Power View
  • Installation and/or enabling the add-ins
Different ways to access your data, clean and merge multiple data sources.

  • Getting data from a file (text versus Excel files), folder and a database
  • Filtering unwanted rows
  • Column manipulations
    • Splitting text, (un)pivoting, keep/remove columns, filling blanks, data type checks, transposing, conditional columns, etc.
  • Data categorization/segmentations –grouping, conditional columns
  • Combining data sets –using the Merge Queries versus Append Queries feature
  • Different ways to load the re-shaped data
How to get your already connected data sets to work together

  • Creating and managing relationships –lookup columns
  • Linked tables
  • Calculated columns and measures –what is the difference? Using examples
  • Adding a date dimensions table -various approaches, sorting months
  • Defining hierarchies –dates, products, regional hierarchies
  • Hiding unnecessary columns/tables from user view
Write DAX calculations to enrich your reports and generate powerful insights

  • What is context? Row versus filter context
  • Modifying filter context using CALCULATE function
  • Analyzing data over time (time intelligence)
    • Current period versus prior period (DATEADD, SAMEPERIODLASTYEAR, DATESBETWEEN, etc.)
    • Cumulative calculations (YTD, QTD, MTD)
  • Controlling filters –ALL, ALLEXCEPT, FILTER
  • Integrating parameter tables in your measures (Actual, QTD, YTD filter table)
  • Working with different charts –adding and customizing for professional looks
  • Leveraging on conditional formatting to draw attention to key information
  • Non-standard charts in Excel –progress bar/doughnut/waffle charts
  • Working with shapes and the camera tool
  • Custom number formatting
  • Adding slicers and connecting to multiple pivot reports
  • Customizing slicers and pivot table designs for branded slicers and pivot reports

20mar(mar 20)8:30 am22(mar 22)4:00 pmFeaturedLarge Data Analysis with Excel Power Pivot & Power QueryCreate discoverable reports with high-level comparatives based on multiple data sources8:30 am - 4:00 pm (22) The Heron Portico Hotel, Nairobi

Reserve your seat now

What you get:

  • 18 hours of practical sessions

  • A step-by-step guide

  • After-course instructor support

  • Certificate of participation

Do you have 8 or more staff who could benefit from this program?

Run this course in-house