Collect & Transform Data Using Power Query in Excel & Power BI ISI-1522

Using Power Query you can import, reshape and cleanse any data from a simple interface, so you can mine that data for all of its hidden insights. Power Query is embedded in Excel, Power BI and other Microsoft products. Discover how to eliminate time-consuming manual data preparation, solve commons problems, avoid pitfalls and more

• 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.
• Describe classic data analysis with Excel
• Describe Excel pivot tables
• Describe the limitations of classic data analysis with Excel

Module 2: The Excel Data Model

This module looks at the classic Excel data model and at ways to extend it.

• Describe an Excel data model
• View data within an Excel data table
• Describe DAX

Module 3: Importing Data from Files

This module looks at pre-formatting and importing CSV files.

• Import data into excel.
• Shape and transform data.
• Load data.

Module 4: Importing Data from Databases

This module looks at how to import data into Excel from a SQL Server database.

• Identify available data sources.
• Preview, shape, and transform data.
• Explain table relationships and hierarchies.
• Load data from various sources.

Module 5: Importing Data from Excel Reports

This module describes how to import data from a report.

• Import data from Excel reports.
• Transform Excel report data.

Module 6: Creating and Formatting Measures

This module describes how to create and format measures.

• Explain what DAX is and when to use it.
• Describe a measure.
• Use some of the advanced functions within DAX.

Module 7: Visualizing Data in Excel

This module describes how to visualize data in Excel.

• Create and refine a pivot chart.
• Describe cube functions and when to use them.
• Describe a number of charts for use with cube functions.

Module 8: Using Excel with Power BI

This module describes how to use Excel with Power BI.

• Describe Power Bi and the various versions available.
• Upload Excel data to Power BI.
• Describe the Power BI App.

Before attending this course, students must have:

• Basic knowledge of the Microsoft Windows operating system and its core functionality.
• Working knowledge of relational databases.
• Extensive knowledge of Excel spreadsheets including formulas, charts, filtering, sorting, and sub-totals.
• Basic knowledge of data analytics equivalent to having attended course 10994.