This Microsoft Official Course is designed to give students the ability to add BI (business intelligence) techniques to Excel data analysis. Take your Excel reports up a notch with this hands-on course. We will start by looking at how Excel reports have been historically built – with lots of VLOOKUPS, clunky formulae, tables and charts. We’ll share how to transform this traditional Excel report into a modern, engaging, and interactive report that can be refreshed with the latest, up to date data - with just the click of a button. This course is also known as 20779.

NOTE: Power BI is briefly introduced in the very last module of this course, but it is not the primary focus. If you are looking for Power BI training, we suggest you check out our Power BI Essential Skills and other Power BI specific courses.

Analysts, managers, data scientists and anyone using Microsoft Excel to visualize data. If you want to get actionable insights from an Excel report, this is the course for you.

We recommend that students have knowledge equivalent to the topics covered in our Excel Essential Skills, Excel Timesaving Features and Excel Data Analysis and Reporting courses, OR:

• Be comfortable writing and working with intermediate to advanced Excel formulas.
• Demonstrate how to write a successful VLOOKUP.
• Have used or created a PivotChart or PivotTable.
• Have the desire to work with data from multiple tables or sources (might currently be using VLOOKUP, copy paste or other method to combine data in Excel).

You will see Excel in a brand-new light. The Excel Power Tools (Power Query, Power Pivot and capability to publish to Power BI) make visualizing your data in Excel a breeze. Freeing you up to focus your time on what matters most.

After completing this course, students will be able to:

• Explore and extend a classic Excel dashboard.
• Explore and extend an Excel data model.
• Pre-format and import a .CSV file.
• Import data from a SQL Server database
• Import data from a report.
• Create measures using advanced DAX functions.
• Create data visualizations in Excel.
• Create a Power BI dashboard with Excel.

Note: This course is the official Microsoft course 20779: Analyzing Data with Excel. The outline below

Module 1: Data Analysis in Excel (VLOOKUP, Tables and PivotTables)

Review classic Data Analysis with Excel, including the use of:Excel Tables, VLOOKUP(), Excel Pivot Tables, PivotCharts and slicers. Discuss this issues and limitations these tools.

Module 2: The Excel Data Model (Introduction to Power Pivot)

Use the Excel data model to analyze your data. Learn how to quickly format the data in your charts and tables by using Power Pivot. Use DAX to create calculated columns and measures to extend your data model.

Module 3: Importing Data from Files (Introduction to Power Query)

Get & Transform Data with Power Query. In this module we will see how you can easily import a single CSV file or even an entire folder of files (imagine weekly exports from your financial system) directly into Excel.

Module 4: Importing Data from Databases (Introduction to Relationships in Excel)

Learn how to create relationships between different sources in Excel. Import data from relational databases, such as SQL Server database, or from CSV, Excel files or even the web and create relationships in Excel. We will also touch on the importance of having a Date table in your data model and explore hierarchies (such as Year, Quarter, Month, Day).

Module 5: Importing Data from Excel Reports (Transform Data with Power Query)

Learn how to navigate Power Query Settings and Applied Steps to split columns, fill and copy data down to the next value, unpivot data, and more. Save and apply the from Power Query and next time you need this report updated, you’ll only need to click the “Refresh” button.

Module 6: Creating and Formatting Measures (Introduction to DAX)

Learn what DAX is and how you can use it to extend your reporting. In this module you will learn the difference between measures and columns. Use some advanced DAX functions, such as CALCULATE, DATEADD, and DATESYTD, to create measures which calculate: Last year comparison, year to date totals, and market share.

Module 7: Visualizing Data in Excel (Excel Dashboards)

In this module we will create a series of PivotCharts and display them in one page. Add slicers to filter all charts with one click. Change the chart type to suit your requirements.

OPTIONAL: Not all chart types are available as PivotCharts, but it is possible to convert a PivotTable into Cube Functions. The cells with these cube functions can then be referenced to build treemaps, histograms, pareto charts and more.

Module 8: Using Excel with Power BI

Publish an Excel report to Power BI web service and view your report online. Power BI is a separate Microsoft application which enables you to create more engaging and interactive visualizations to further extend the Excel reporting capabilities.