Power Pivot training2019-02-28T19:34:50+03:00

DATA MODELING & ANALYTICS

with

POWER QUERY & POWER PIVOT

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

…and did I tell you?

Kill 2 birds with 1 stone

By investing in this Power Query & Power Pivot training, you’ll have learnt over 70% of the fundamentals that you need to know in Power BI

3 Expert-Led Days of Empowering You to Save Time by Eliminating Repetitive Tasks

10jul(jul 10)8:30 am12(jul 12)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 (12) The Heron Portico Hotel, Nairobi

Your Investment: KES 29,500 (VAT Incl.)

Download Course Details

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. Carry your laptop with MS Office (2010 and above) installed.
  2. Tea/Coffee, Snacks and Meals are provided in this training.
  3. Parking is available
  • 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
Reserve your seat 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

Grow your career with this FREE ONLINE video course

Zero to Pro with Excel Pivot Tables

  1. Pivot table fundamentals
  2. Save time with advanced hidden concepts
  3. Interactive Pivot-driven dashboard
  4. Intro to Power Pivot & Power Query
  5. Over 10 video lessons
JOIN NOW FREE
close-link
X