Overview
This course aims to help participants master the ability to add BI techniques to Microsoft Excel data analysis. The course also covers concepts of Pivot charts, the Excel data model and PowerBI, going above and beyond just basic tables and charts commonly used and analyzed in Microsoft Excel. The course would also help SQL Server report creators to discover alternative methods of presenting their data.
What You'll Learn
- 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
Curriculum
- Classic data analysis with Excel
- Excel pivot tables
- Limitations of classic data analysis
- Lab: Building a classic Excel dashboard
- Formatting data
- Building a Pivot table
- Adding Pivot charts and a slicer
- Using an Excel data model
- DAX
- Lab: Explore an Excel data model
- Create calculated columns
- Format data model data
- Create measures
- Analyze the data
- Importing data into Excel
- Shaping and transforming data
- Loading data
- Lab: Importing data from a CSV file
- Import and transform data from a CSV file
- Add data from a folder
- Available data sources
- Previewing, shaping and transforming data
- Table relationships and hierarchies
- Loading data
- Lab: Import data from multiple sources
- Import data from SQL server
- Import data from CSV file
- Create a data table
- Importing data from Excel reports
- Transforming Excel report data
- Lab: Importing data from a report
- Import data from Excel
- Transform the Excel data
- Load the data into an Excel data model
- DAX
- Measures
- Advanced DAX functions
- Lab: Creating measures using advanced DAX functions
- Last year comparison
- Year to date
- Market share
- Pivot charts
- Cube functions
- Charts for cube functions
- Lab: Data visualization in Excel
- Create a tabular report
- Create a Pivot chart
- Add slicers to charts
- PowerBI
- Uploading Excel data to PowerBI
- PowerBI mobile app
- Lab: Creating a PowerBI dashboard with Excel
- Uploading Excel data
- Creating a PowerBI dashboard
- Using PowerBI desktop
Who should attend
The course is highly recommended for participants who are experienced with analyzing data with Excel and who are keen to learn effective BI techniques. It is also very useful for SQL Server report creators who wish to learn new alternatives for presenting the data.
Prerequisites
Participants need to have –
- Basic knowledge of Microsoft Windows operating systems and its core functionality
- Working knowledge of relational databases
- Extensive knowledge of Excel spreadsheets including formulas, charts, filtering, sorting and sub-totals
- Have knowledge equivalent to the course Data Analysis Fundamentals using Excel
Interested in this Course?
Certification
This course helps participants prepare for the two certification exams that are required for earning the Microsoft Certified Solutions Associate: BI Reporting (MCSA: BI Reporting) certification. The certification is useful for building a professional career in BI reporting, as it demonstrates the participant’s knowledge in the field of data analysis, data visualization, modeling, dashboards, and direct connectivity to data sources in Excel and Power BI.
The MCSA: BI Reporting is the first step towards earning the Data Management and Analytics Microsoft Certified Solutions Expert (MCSE) certification.
