SPECIALISTS IN FINANCIAL MODELLING
0 item(s) $0.00 AUD + GST

World Class Power BI, Power Pivot and Power Query Training - Sydney

Date: Monday, 30 January 2017 - 9:00am - Tuesday, 31 January 2017 - 5:00pm

Location: Sydney

Duration: 2 Days - $1,495 plus GST

LEARN THE MOST USEFUL CAPABILITIES OF POWER PIVOT

A remarkable simplicity underlies the amazing power of Power Pivot. In fact, the most useful capabilities are conveniently the most learnable. This training focuses on that set – the things anyone can learn AND retain in a short period of time, while simultaneously building a confident foundation for future learning.

This course is being hosted by an external provider.  Please register here and we will send you some further information.

BENEFITS OF ATTENDING

  1. Understand Power Pivot and how to apply it to your job.
  2. Achieve better and faster results than with Excel alone.
  3. Produce valuable metrics for your business that never existed before.
  4. Learn “recipes” to solve complex problems & create standout work.

WHO SHOULD ATTEND?

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

COURSE OVERVIEW 

PowerPivot is a product built on 15 years of database expertise at Microsoft.  PowerPivot is a super powered product that is embedded in Microsoft Excel – all you need to do now is learn how to use it.  And the good news is that the most useful capabilities are conveniently the most learnable.

This learning strategy is built on four pillars:

  1. Fundamentals – short and simple, these fit onto a single 6-step flowchart (provided).
  2. Patterns – “recipes” that you can easily follow and modify to solve business problems, without requiring PhD-level mastery.
  3. Priorities – Focused on the capabilities that are most “bang for the buck.”  Avoids topics that are too complex or too “niche.”
  4. Examples – there is no reference guide quite like a working example that you can revisit when needed.  Each student takes home dozens of such examples in workbook form.

FOR ATTENDING POWER PIVOT FOR EXCEL YOU GET

  • Exposure to best-in-class Power Pivot techniques, “recipes”,
  • & practical tips.
  • A digital copy of the book “DAX Formulas for Power Pivot”.
  • USB stick with example workbooks, sample data, and PDF guides.
  • Networking Opportunities

PREREQUISITES

SUGGESTED EXPERIENCE

  • 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 SOFTWARE

Power Pivot requires Excel 2010 or 2013. 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 or 2013 is necessary when you return to the workplace.

COURSE SCHEDULE

Day One

8:45  Check-in

9:00  Introduction

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

9:30  Loading Data

  • Various Methods
  • Tradeoffs
  • Best Practices

10:30  1st Break

10:45  Working with Multiple Tables

  • 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

12:30  Lunch

1:00  Intro to Formulas and DAX

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

2:30  2nd Break

2:45  The Magic of Calculated Fields (Measures)

  • The dramatic benefits of “Portable formulas”
  • CALCULATE() - Your New Best Friend, and How Measures Actually Work.
  • ALL(), percentage of totals, and "canceling slicers."
  • Introduction to Time Intelligence: Date/Calendar Tables
  • Running totals – Year to Date, Month to Date, Quarter to Date
  • Change versus Prior Month/Year Etc.

4:00  Exercises with trainer support to cement your learning

5:00  End of Day One

Day Two

9:00  Recap and More DAX

  • Recap of previous day
  • Filter() , Custom Calendars.

10:30  Break

10:45  More DAX

  • Values()
  • Counting things that didn’t happen (eg customers that haven’t purchased)

            Excel’s Hidden Gems Unveiled

  • Cube Formulas - Blowing Up Your Pivot Tables Yields Amazing Results
  • Named Sets - Re-Useable Sort/Filter Settings, "Asymmetric" Reporting

12:00  Exercises with trainer support to cement your learning

12:30  Lunch

1:30    Power Query

  • When to use it and why
  • Built-in transformations
  • The “M” language

2:00    Power BI and SharePoint

  • Different Web Hosting options
  • How to give users a positive web browsing experience that doesn’t look like Excel
  • Options to refresh workbooks

2:45  Break

3:00  Power Patterns

  • Working with Custom Calendars - "445" and Similar
  • Disconnected Slicers - "What If" Pivots and Other Magic
  • Adding a "Last refreshed date" to your dashboards
  • Many to Many - what is it and how to deal with it

3:45  Performance

  • How to Keep Your Files Small and Your Calculations Fast
  • The Five Guidelines

4:30  End of Day Two

This comprehensive two day workshop is $1,495 plus GST.

This course is being hosted by an external provider.  Please register here and we will send you some further information.