Microsoft Confirm Excel’s Role in Business Intelligence with MCSA Exam 70-779

Microsoft’s MCSE (Microsoft Certified Solution Expert) and MCSA (Microsoft Certified Solution Associate) qualifications do not normally include an assessment of an IT pro’s ability to use Microsoft Excel. However, this has all changed with the inclusion of Exam 70-779 as a requirement for obtaining the MCSA: BI Reporting.

The introduction of the Power Pivot add-in for Excel, back in 2010, and Power Query in 2013, revolutionized the program’s capabilities as a data analysis tool. Power Pivot, Power Query and the DAX and M languages enable Excel users to perform complex data analysis and build sophisticated data models by mashing up data from a wide variety of sources.

With the release of Power BI Desktop in 2015, many assumed that Microsoft intended to move their self-service BI strategy away from Excel. However, Power Pivot and Power Query are now built into the program and it looks as though Excel will continue to be a key part of Microsoft’s self-service business intelligence solution.

G Com Solutions will be offering a 5-day Power BI Certification Training Course for Exam 70-779 from April onward. This course will include the exam, on day 5, and will immediately follow our existing 5-day Power BI Certification Training Course for Exam 70-778. Delegates who take both courses and pass the two exams will obtain the Micorosoft MCSA: BI Reporting.

The skills measured by Exam 70-779 are shown below.

Consume and Transform Data by Using Microsoft Excel (30-35%)

Import from data sources

• Connect to and import from databases, files, and folders
• Connect to Microsoft SQL Azure
• Big Data
• SQL Server Analysis Services (SSAS) and Power Query
• Import supported file types
• Import from other Excel workbooks
• Link to data from other sources

Perform data transformations

• Design and implement basic and advanced transformations
• Apply business rules
• Change data format to support visualization
• Filter data
• Format data

Cleanse data

• Manage incomplete data
• Handle data received as a report
• Remove unnecessary rows
• Columns
• And data
• Remove repeating headers

Model Data (35-40%)

Create and optimize data models

• Manage data relationships
• Optimize models for reporting
• Manually enter data
• Perform Get & Transform
• Create automatic relationships
• Create manual relationships

Create calculated columns, measures, and tables

• Create DAX queries
• Create DAX formulas
• Create Excel formulas

Create performance KPIs

• Calculate the actual value
• Calculate the target value
• Calculate actual to target values

Create hierarchies

• Create date hierarchies
• Create business hierarchies
• Resolve hierarchy issues

Visualize Data (30-35%)

Create and manage pivottables

• Format pivottables and pivotcharts
• Format calculated measures
• Filter data
• Group and summarize data

Create and manage pivotcharts

• Select a chart type
• Format calculated measures
• Filter data
• Group and summarize data
• Use slicers

Interact with Power BI

• Import Excel data from Power BI
• Manipulate Excel data in Power BI