Learn how you can generate rich and interactive dashboards that look like they have spent weeks in development, in only a fraction of the time.  Leverage Power Query and Power Pivot in Excel, and how they can interact with Power BI to analyse and report key information.

Power Pivot, Power Query and Power BI are tools that enable database-like computational power on our desktops, firstly within the familiar environment of Excel, and then within Power BI as it takes, automates and elevates the process of reporting and generating dashboards.

This self-paced module will develop a sophisticated case study exploiting many of the powerful tools and functionalities. 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.

This course includes the Power Pivot, Power Query and Power BI course modules, bundled together for a discounted price!

Learning Outcomes:

Upon satisfactory completion of this activity you will be able to:

• Load, link and update data in Power Pivot.
• Write formulae in Power Pivot’s Data Analysis Expressions (DAX) language.
• Use Time Intelligence functions to analyse data across periods.
• Import data using Power Query from a range of sources.
• Format and transform ’dirty’ data into a usable form to enable analysis.
• Build intuitive and interactive 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