Power Pivot & Power BI For Excel

Are you relying on your IT to create Data Models &  Business Intelligence?

If you want to become good at creating Self Service Business Intelligence (BI) using the excellent and extensive Microsoft tool sets, you must learn how to become a data modeller using Power Pivot.

In this practical, hands-on Power Pivot & Power BI workshop, this is exactly what you will learn.  Our expert approach in this classroom training course makes it accessible to become an Excel Pro.
At the end of the 2 days you will be well on you way to becoming a Power BI, Power Pivot and Power Query Ninja!

This Advanced Data Analytics course teaches the skills a business analyst needs to know in order to successfully use Power BI, Power Pivot for Excel and Power Query. The skills are completely transferable between Power BI and Excel so there are lots of synergies. The course starts out first using Powerful Advanced Excel functionality and then moves to Power BI tools.

The Power Pivot & Power BI Training covers:

  1. Importing and creating relationships between tables of data in Excel.
  2. Understanding the optimal data structure to use in Power Pivot.
  3. Writing basic Data Analysis eXpressions (DAX) formulas to extract business insights.
  4. Then writing more advanced DAX formulas that use the power of DAX to extract additional business insights from the data that are not immediately obvious (yet are inherently available).
  5. An introduction and overview of Power Query, how to use the tool and why it is so great.
  6. Helping Excel professionals move from being a Power Pivot for Excel user to being a Power BI user, covering the differences between the tools and how to start using Power BI effectively.

The Main objective of this training is to make you understand the Power BI & Power Pivot offering available in Microsoft Excel, and be a Power Ninja in using Excel for Data Analysis & Business Intelligence Reporting.

  1. Understand Power Query & Power Pivot and how to apply it to your job.
  2. Achieve better and faster results than with Microsoft Excel alone.
  3. Produce valuable metrics for your business that never existed before.
  4. Learn best tips & tricks to solve complex problems & create stunning work.

Logistics

This 2 Day Excel Power Pivot & Power BI Masterclass will be held in Singapore.

Tea/Coffee, Light Snacks are provided in this training.

  • Intensive users of Excel, specially those responsible for reporting and analysis.
  • Business leaders who need to mine their data for actionable insight – using existing resources and on short deadlines.
  • Organizations looking to leverage Microsoft’s groundbreaking Power Pivot & BI offerings.
  • Business Intelligence and Database professionals looking to increase their professional agility and dramatically expand their capabilities.

Day One

Introduction

  • When and Why to Use Power Pivot
  • Tour of the Power Pivot Environment

Loading Data

  • Various Methods
  • Tradeoffs
  • Best Practices

Introduction to Formulas and DAX using Power Pivot for Excel

  • Named reference syntax
  • Similarities to traditional Excel
  • Measures (Calc Fields) vs. Calc Columns
  • COUNTROWS() and DISTINTCOUNT()
  • Best practices

Working with Multiple Tables using Power BI

  • Relationships: the End of VLOOKUP?
  • Data Tables vs. Lookup Tables
  • Multiple Data tables in a single model
  • Troubleshooting your “relationship problems”
  • Exercises to cement your learning

The Magic of DAX Measures using Power BI

  • The CALCULATE() function.
  • ALL(), percentage of totals, and “canceling slicers.
  • Introduction to Time Intelligence: Date/Calendar Tables
  • Running totals – Year to Date,
  • Change versus Prior Month/Year Etc.

What-if Analysis using Disconnected Tables and Slicers

Day Two

Recap and More DAX

  • Recap of previous day
  • Theory of the Vertipaq engine
  • Filter() , Custom Calendars.

DAX Patterns for common problems using Power BI

  • Counting things that didn’t happen (eg customers without purchases)
  • Custom time intelligence patterns

Power Query

  • When to use it and why
  • Built-in transformations
  • The “M” language
  • Using Power Query to solve real world problems.

Performance

  • How to Keep Your Files Small and Your Calculations Fast
  • Theory behind compression
    Sharing and Refreshing Workbooks
  • Different Web Hosting options (SharePoint and Power BI)
  • Options to refresh workbooks

Power BI Visuals

  • A detailed review of what is different about Power BI vs Excel.
  • Learn to think differently and use the visualisations available.

Power BI Service

  • A detailed review of the cloud offering including sharing, Q&A, Analyze in Excel, Dashboards

Excel’s Best Kept Secret Unveiled

  • Cube Formulas

General Q&A

It would be good if you have Basic knowledge of Excel, and can write simple formulas like SUM, IF, Nested IF, and:

  • Light usage of Excel PivotTables (or similar data analysis tools)
  • Familiarity with Excel functions like SUMIF and VLOOKUP are a plus
  • A passion for acquiring Data Superpowers

Note: Anyone with Database, BI, Programming, or similar experience are quite qualified to take this course, even if lacking in Excel experience.

NOTE ABOUT MICROSOFT EXCEL POWER PIVOT SOFTWARE

Power Pivot requires Excel 2010 or 2013 or Excel 2016. If you have sufficient Excel experience with Excel 2007 or earlier, you can still succeed in the course, however, to maintain learned skills Excel 2010, 2013 or 2016 is necessary when you return to the workplace.

The Main objective of this training is to make you understand the Power BI & Power Pivot offering available in Microsoft Excel, and be a Power Ninja in using Excel for Data Analysis & Business Intelligence Reporting.

  1. Understand Power Query & Power Pivot and how to apply it to your job.
  2. Achieve better and faster results than with Microsoft Excel alone.
  3. Produce valuable metrics for your business that never existed before.
  4. Learn best tips & tricks to solve complex problems & create stunning work.

Logistics

This 2 Day Excel Power Pivot & Power BI Masterclass will be held in Singapore.

Tea/Coffee, Light Snacks are provided in this training.

Go To Top

  • Intensive users of Excel, specially those responsible for reporting and analysis.
  • Business leaders who need to mine their data for actionable insight – using existing resources and on short deadlines.
  • Organizations looking to leverage Microsoft’s groundbreaking Power Pivot & BI offerings.
  • Business Intelligence and Database professionals looking to increase their professional agility and dramatically expand their capabilities.

Go To Top

Day One

Introduction

  • When and Why to Use Power Pivot
  • Tour of the Power Pivot Environment

Loading Data

  • Various Methods
  • Tradeoffs
  • Best Practices

Introduction to Formulas and DAX using Power Pivot for Excel

  • Named reference syntax
  • Similarities to traditional Excel
  • Measures (Calc Fields) vs. Calc Columns
  • COUNTROWS() and DISTINTCOUNT()
  • Best practices

Working with Multiple Tables using Power BI

  • Relationships: the End of VLOOKUP?
  • Data Tables vs. Lookup Tables
  • Multiple Data tables in a single model
  • Troubleshooting your “relationship problems”
  • Exercises to cement your learning

The Magic of DAX Measures using Power BI

  • The CALCULATE() function.
  • ALL(), percentage of totals, and “canceling slicers.
  • Introduction to Time Intelligence: Date/Calendar Tables
  • Running totals – Year to Date,
  • Change versus Prior Month/Year Etc.

What-if Analysis using Disconnected Tables and Slicers

Day Two

Recap and More DAX

  • Recap of previous day
  • Theory of the Vertipaq engine
  • Filter() , Custom Calendars.

DAX Patterns for common problems using Power BI

  • Counting things that didn’t happen (eg customers without purchases)
  • Custom time intelligence patterns

Power Query

  • When to use it and why
  • Built-in transformations
  • The “M” language
  • Using Power Query to solve real world problems.

Performance

  • How to Keep Your Files Small and Your Calculations Fast
  • Theory behind compression
    Sharing and Refreshing Workbooks
  • Different Web Hosting options (SharePoint and Power BI)
  • Options to refresh workbooks

Power BI Visuals

  • A detailed review of what is different about Power BI vs Excel.
  • Learn to think differently and use the visualisations available.

Power BI Service

  • A detailed review of the cloud offering including sharing, Q&A, Analyze in Excel, Dashboards

Excel’s Best Kept Secret Unveiled

  • Cube Formulas

General Q&A

Go To Top

It would be good if you have Basic knowledge of Excel, and can write simple formulas like SUM, IF, Nested IF, and:

  • Light usage of Excel PivotTables (or similar data analysis tools)
  • Familiarity with Excel functions like SUMIF and VLOOKUP are a plus
  • A passion for acquiring Data Superpowers

Note: Anyone with Database, BI, Programming, or similar experience are quite qualified to take this course, even if lacking in Excel experience.

NOTE ABOUT MICROSOFT EXCEL POWER PIVOT SOFTWARE

Power Pivot requires Excel 2010 or 2013 or Excel 2016. If you have sufficient Excel experience with Excel 2007 or earlier, you can still succeed in the course, however, to maintain learned skills Excel 2010, 2013 or 2016 is necessary when you return to the workplace.

Go To Top

Learn By Doing

You learn best when you Do It Yourself.

We teach you, step by step, how you can learn new skills, build your knowledge and enhance your career prospects quickly & easily, with Practical Tips & Tricks!

Do You Have a Question or Need a Quotation?

Simple. Just tell us what you need below. We’ll be glad to help you!

Some of the companies that experienced our trainings

How To Register

Register for the 2 Days of Power Pivot & Power BI training today by simply clicking on the chosen course date available at the top of this page.

Need Help?

  1. Call us at +65 6296-2995, SMS / WhatsApp: +65 9066 – 9991
  2. Send an email  to  training@intellisoft.com.sg  OR
  3. Submit an online enquiry if you have  any questions regarding Power Pivot & Power BI Masterclass training.

We also offer Corporate Trainings for this Advanced Excel Power Pivot & Power BI Workshop, if you have a group of 10 or more people. We can arrange to conduct the Power BI training at your office location too.

Learn
Power Pivot & Power BI
For Excel

Free Tips, Tutorials & Training Grants Info

Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Project Management, IT, Soft Skills & more with our Email Newsletter.
Plus get the latest news on Grants. Join Today!