Power Query - Advanced (2 d)

 Training Content

You have been putting Power Query in practice for many months and have encountered many obstacles and roadblocks, this training session helps you to acquire a thorough knowledge and gain deeper insights into the Power Query engine.

• The role of Power Query and the M-language in the Power BI landscape

 ○ Excel 

 ○ PowerBI-models 

 ○ Power BI dataflows 

 ○ Analysis Services 

 ○ Custom Connectors 

 ○ Azure Data Flows 

• M-Language - concepts and architecture

 ○ Evaluation model 

 ○ Expressions and variables, including 'let' 

 ○ Data structures and types

  + Tables 

  + Records 

  + Lists 

  + 'any' 

  + Other data types 

• Custom functions

 ○ The structure of a function 

 ○ Creating functions manually 

 ○ Creating functions through the UI 

 ○ A function in disguise 

• Environments and scope in Power Query

 ○ Global Environment 

 ○ Expression and Variable environments 

 ○ Expression.Evaluate 

• Iteration and Recursion

 ○ Row level function invocation pattern 

 ○ Recursive functions 

 ○ List.Generate 

 ○ List.Accumulate 

• Error Handling

 ○ Try 

 ○ Otherwise 

• Performance

 ○ The Performance Analyzer 

 ○ Query Folding 

 ○ Table.Buffer and List.Buffer 

 ○ Table Keys on joins 

 ○ GroupKind 

 ○ Memory considerations 

• From static to dynamic

 ○ How to plan for change 

 ○ Coding guidelines 

• Organizing queries

 ○ ETL-approach 

 ○ Define once, use many 

 ○ Using parameters 

 ○ Impact on performance 

 Goal

After this session, you will feel confident to face new challenges that previously seemed impossible. You will function as an authority / coach within your company to help colleagues with their projects so they learn from the best.  

 Prerequisites

Own Laptop 💻 + external mouse  

Power BI Desktop (see Microsoft's documentation Get Power BI Desktop). 

You can get Power BI Desktop in two ways, each of which is described in the following sections:

• Install as an app from the Microsoft Store.
• Download directly, as an executable you download and install on your computer.

📖 Documentation

Students will get 2-year access to the on-line in2Facts documentation platform

🚫 Cancellations

Cancellations up to 8 days before the training course are at no charge. 

Cancellations after this date, as well as no-shows, will be liable for the full registration fee.