Business intelligence redefined
What are the three key buttons in your Management Information System? You're in the majority if they're "OK", "Cancel" and "Export to Excel". Given there’s no point enhancing your forecasting tools and techniques if your current business performance can’t be assessed, why not use Excel and its related tools fully to assist your analysis?
Beginning with the introduction of Power Pivot and Power Query in Excel 2010, and moving onto their evolved form in Power BI, we now have database-like computational power on our desktops, firstly within the familiar environment of Excel, and then within Power BI Desktop as it takes, automates and elevates the process of reporting and generating dashboards.
This course shows you how you can design rich and interactive analysis tools that look like they have spent weeks in development. In short: this is deeper business insight in a fraction of the time, using intuitive tools that can be readily obtained and implemented.
Key learning through a practical case study
This two-day course assumes little pre-existing knowledge other than basic-to-intermediate Excel functionality. We will develop a sophisticated case study exploiting many of the powerful tools and functionalities from a standing start. Using hands-on examples throughout, you will learn how to convert mountains of data into key business information efficiently. Key management reporting, variance analysis and KPIs can be delivered faster than you can imagine.
Key benefits
- Understand how Power Pivot and Power Query build on the functionality in Excel’s native tools, such as PivotTables, Slicers and key analytical functions
- Be able to import data from a variety of electronic sources and relate them quickly to deliver key information in minutes not weeks
- Derive best practice database design, realising the power of the Data Model by using LOOKUP lists and efficient database design
- Learn how to write powerful formulae in PowerPivot's Data Analysis Expressions (DAX) language.
- Convert your knowledge of Excel into powerful and intuitive dashboards by using Power BI to automate your reports.
Programme Outline
- Recap on current Excel techniques: SUMIF, SUMIFS, SUMPRODUCT, Tables and PivotTables
- Loading, linking and updating data in Power Pivot
- Constructing efficient databases for Power Pivot analysis
- Creating calculated columnsBuilding Measures / Calculated Fields
- Key DAX functions: e.g. ALL, BLANK, CALCULATE, FILTER, HASONEVALUE, IF
- Using unlinked tables for sophisticated analysis
- Creating Date tables and using the powerful Time Intelligence functions
- Importing data using Power Query from a range of sources
- Transforming data – splitting columns, pivoting and unpivoting results, cleaning data inputs
- Appending queries together, and bringing whole folders of files into Excel
- Setting up Power Pivot and Power Query solutions within the Power BI interface
- Building intuitive and interactive reports using Power BI Desktop
- Unlocking the power of cloud analytics with the Power BI Service
- Setting up scheduled report updates
Who should attend
Excel users who believe that PivotTables are insufficient, business analysts and financial/management accountants working with large amounts of data and seeking rapid career progression, key executives wanting to enhance and shorten their business decision cycles
