Description
Link to the agenda and sample videos from the course
Length: 5 Hours and 20 Minutes
Instructor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:Â https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and user groups. And He is a Microsoft Certified Trainer.
Modules
Introduction to Power Query and Get Data
This module brings you to the world of Power Query. First it starts with an introduction sample of connecting to web data sources, and applying some data preparation operations. Then it will continue with explaining other types of data sources such as Excel and SQL Server. You will learn about value structures and data types in Power Query. And also you will learn some specific data source types such as JSON. All scenarios will be explained through examples.
Lessons
- Introduction to Power Query
- Get Data from Excel and SQL Server
- Value Structures in Power Query
- Data Types in Power Query
- Get Data from JSON
- Convert Timestamp to Date Time
Query Operations
Every dataset considered in Power Query as a QUERY. In this module, you will learn what are operations that you can do on queries. Operations such as; Combining Queries, referencing or duplicating queries, and grouping queries in a folder structure.
Lessons
- Query Operations
- Merge – Join – Queries
- Merge Join Types
- Append Queries
- Folder Structure for Queries
Step Operations
Power Query is an step by step transformation tool. You can apply some operations on every step. In this module you will learn about how to manage steps, configure them, and also you will learn about viewing native query and tips related to that.
Lessons
- Step Operations
- Query Folding – Performance Tip
Column and Row Transformations
There are a number of transformations that can be applied on columns and rows. Column transformations such as selecting columns, re-ordering, removing columns will be explained. for row transformations you will learn about filtering data and tricks about it, removing or keeping rows from start or end of the dataset. you will learn about removing error rows or duplicates as well through many examples.
Lessons
- Column Operations
- Row Operations
- Filtering Data
- Sorting Data
Extra Operations and Undo
Some specific operations on queries will be explained in this short module; such as drill down or add as new query. You will learn usage of these operations through examples. Also you will learn about things you need to consider when undo-ing an operation in Power Query.
Lessons
- Add As New Query and Drill Down
- Undo for Operations
Table Transformations
Tables are the most common type of datasets in Power Query. You can apply some transformations such as using their first row as header or reverse. you can get the row count of a table, or transpose. You will learn about these operations through this module.
Lessons
- Table Transformations
- Group By Free Preview
- Advanced Group by Using M
Specific Column Transformations
Every type of column, has a number of possible transformations. In this module, you will learn about transformations that you can apply on ANY types of columns; such as change type, fill down, up. Also specific transformations such as Text, Numeric, Date Time, and structured column transformations will be explained.
Lessons
- Change Type Column Transformation
- Fill Down and Up Transformations
- Pivot and Unpivot
- Text Transformations – Split and Merge
- Extract and Format Text Transformations
- Numeric Transformations
- Date and Time Transformations
- Structured Column Transformation
Add Column
In this module, you will learn how to add a column with transformation. Adding a column as Index, and also conditional column will be explained through examples.
Lessons
- Add Index and Duplicate Column
- Add Column Tab vs Transform Tab
- Add Conditional Column
- Add Custom Column
M Scripting
M is the Power Query scripting language. Every transformation will be written in this language. You cannot be a Power Query expert without learning M. This section will give you a full understanding of M scripting language. You will learn about syntax structure of the language. You will also learn about some of the features that are not yet fully implemented in the graphical user interface, however it is available in M scripting. The module is full of sample codes and examples.
Lessons
- Introduction to M: Power Query Formula Language
- Learning M Syntax
- Custom Functions
- Generators Free Preview
- EACH Singleton Function
- Sample Demo for Custom Function, Generator, and EACH
- Error Handling
Sample Power Query Use Cases
It’s time now to go through some of the learning from this course through real-world use cases. This module goes through some real-world use case samples such as creating a full end to end date dimension and also looping through files in a directory and combining them together.
Lessons
- Creating Date Dimension Part 1 – Calendar Columns
- Creating Date Dimension Part 2 – Fiscal Columns
- Creating Date Dimension Part 3 – Public Holidays
- Combining Multiple CSV Files from a Folder
Reviews
There are no reviews yet.