Power Pivot training2022-07-27T09:01:04+03:00

Physical, instructor-led Training

WED 21 – FRI 23 SEP 2022, 8:30am-4:00pm

at The Heron Portico Hotel, Nairobi

KES 29,900 19,500

DATA MODELING & ANALYTICS

with

EXCEL POWER QUERY & POWER PIVOT

Enroll Now
virtual Power Query Training

Analyze huge data sets (millions of rows) with speed with Excel’s Power Pivot

You’ll agree with me that a bigger chunk of your reporting is spent getting your data ready

In short

Retrieving, reshaping and/or combining data from various sources can be a great time waster, leaving you with little or no time to analyze for insights –that is missed opportunities!

If you’re looking for a way to automate the very mundane tasks (without coding –using point and click features on the ribbon!) …

…and yes, most probably VBA or Excel Macros is not your thing

Then

You need this course!

Power Query will keep sequential record of the steps you do the first time and will repeat them with new data –NO RE-WORK! You simply hit the Refresh button when new data knocks

…

Power Pivot on the other hand will take the cleaned data and allow you to create measures/calculations that you can use for your report

And the best part?

The formulas are done ONCE and are re-usable EVERYWHERE!

You can therefore focus on turning data into insight –and yes –no more office sleep overs and late-night rush reports

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

Versions & compatibility:

  • If you have Excel 2013 or 2016 Professional Plus edition, Power Pivot 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, Power Query is in-built, and is available in the Data menu >>>Get & Transform.

Logistics:

  1. Access to a laptop with MS Office (preferably 2016 or higher) installed.
  2. Access to stable internet connectivity

  • 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
  • Other DAX formulas –COUNTROWS, HASONEVALUE, SWITCH, RANK, VALUE, RELATED
  • 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
Start Automating your Reports. Register Now

Sharing is Caring

Share this course on your social profiles

FAQs

What is Power Pivot?2019-02-27T17:57:17+03:00

Power Pivot, commonly referred to as the Data Model is an Excel add-in that helps you to create normal Pivot reports that draw data from a variety of sources. It allows you to extend Excel capabilities in two ways: (1) You can connect to multiple data sources, create relationships and report as one, and (2) You get access to powerful formulas built on a language called DAX (Data Analysis Expressions) making calculations such as YTD and prior year comparisons very easy.

Click here for details on Excel versions compatibility

What is Power Query?2019-02-27T17:57:03+03:00

Power Query enhances and simplifies tasks that revolve data access, cleaning, segmenting, grouping and merging. It is a free add-in available in MS Excel 2010 and above. In Excel 2010 and 2013 you download and install for free. With Excel 2016, 2019 and Office 365, it’s inbuilt into the Data menu as Get & Transform

Register before 21st-Oct-2022. Save KES 9,000!

Power BI Training, 9th-11th Nov 2022, Nairobi.

Go to Top