Databases · DBC-56

Advanced Power Query for Power BI

Advanced Power Query for Power BI is a course in the M language and advanced data preparation, covering custom functions, parameters, folder and API sources, JSON, error handling, query folding, Query Diagnostics and incremental refresh. It suits Power BI report builders and data analysts who want data preparation that refreshes faster and copes with messy, changing sources.

Updated 30 Oct 2026

From 5,850 THB / person 6,500 −10% excl. VAT 7% · group rates available

Corporate training quote Join the waiting list

PDFDownload the course outline

Duration12 hours · 2 days
FormatOnsite / live online
Next roundOn request
CertificateIncluded

Course overview

Power Query makes data preparation in Power BI easy to start with clicks, but once data comes from many files with different layouts, from APIs or in larger volumes, click-built queries tend to break when a file changes, refresh too slowly or fail to refresh in the Power BI service. Understanding the M language and how Power Query works under the hood is an essential skill for intermediate and advanced report builders.

This course takes learners deep into Power Query in Power BI. It starts with reading and writing M in the Advanced Editor, working with lists, records and tables, building custom functions and parameters, combining folder and SharePoint files with mismatched layouts, pulling paged data from web APIs and JSON, and handling errors systematically. It then turns to performance with query folding, Query Diagnostics and privacy levels, sets up incremental refresh and refresh in the Power BI service with an overview of dataflows, and closes with a workshop that builds a maintainable data pipeline. (2 days, 6 hours per day, 12 hours in total, Intermediate to Advanced level.)

What you’ll gain

Read, edit and write M code in the Advanced Editor with confidence
Use lists, records, tables and standard functions to solve complex reshaping problems
Build reusable custom functions and parameters
Combine files from folders and pull paged data from web APIs and JSON
Handle errors and check data quality before data reaches the model
Check and preserve query folding and measure performance with Query Diagnostics
Set up incremental refresh for large tables
Prepare queries that refresh successfully in the Power BI service

Who this course is for

Power BI report builders who already use Power Query with clicks and want to go deeper
Data analysts who regularly combine data from many files, ERP systems or APIs
Anyone facing slow refreshes or refreshes that fail in the Power BI service
Power Query users in Excel who are moving their work to Power BI
BI teams who need shared query standards that several people can maintain

Prerequisites

Able to import and transform data in Power Query with clicks, such as Filter, Merge, Append and Unpivot
Able to build basic reports in Power BI Desktop
Experience with Excel formulas or a little programming helps you learn faster
A Windows laptop that can install Power BI Desktop, plus your own or your organisation's Power BI account

Curriculum

Course Details

This course builds on Power Query in Microsoft Excel and goes deep into the M language, performance and refresh in Power BI. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with a lab in every topic. Intermediate to Advanced level. Learners use the free Power BI Desktop on their own Windows laptop, SQL Server Developer Edition for the query folding labs, and their own or their organisation's Power BI account for the service refresh topics. The institute does not provide accounts or licences. The course focuses on data preparation and does not cover data modelling or DAX, which other courses teach. Power BI updates often, so the content follows the latest version on the training day.

Day 1 The M Language and Complex Sources

Section 1: Lab: Understanding M in Depth

Queries, steps and lazy evaluation: how Power Query runs
The let ... in structure and editing code in the Advanced Editor
Primitive values, lists, records and tables, and converting between them
each, _ and lambda functions (=>) hidden inside every step
Staging queries, Reference versus Duplicate, and turning off Enable load
Lab: rework click-built queries so they are readable and maintainable

Section 2: Lab: Standard Functions, Custom Functions and Parameters

Table.TransformColumns, Table.AddColumn and Group By with All Rows
List.Accumulate and List.Generate for iterative work
Write custom functions and turn queries into reusable functions
Parameters for file paths, server names and Dev or Prod environments
Lab: a function that cleans ERP report exports and Thai Buddhist years, reusable in any query

Section 3: Lab: Combining Files from Folders and SharePoint

Automatic Combine Files, and writing your own version that survives change
Files with different headers, sheet names or column counts
Bring file names and dates from metadata in as columns
Connect to a SharePoint or OneDrive folder instead of a local one
Lab: combine branch Excel files with mismatched layouts into one table

Section 4: Lab: Web APIs and JSON

Web.Contents with query strings, headers and RelativePath
Turn deeply nested JSON into tables with records and lists
Pagination: fetch many pages with List.Generate
Keeping API keys safe, and dynamic data source limits when refreshing in the service
Lab: pull a paged public API and combine it into one table

Section 5: Lab: Error Handling and Data Quality

Step-level errors versus cell-level errors
try ... otherwise and try ... catch to handle bad values
Column quality, column distribution and column profile
A data quality query that separates problem rows from the main data
Lab: report problem rows with their causes without failing the refresh

Day 2 Performance, Refresh and Real-world Use

Section 6: Lab: Query Folding

What query folding is and why it matters with large databases
View Native Query and the query folding indicators on each step
Steps that break folding, and reordering steps to keep it
Value.NativeQuery and pushing SQL to the database safely
Lab: rework a SQL Server query so it folds all the way

Section 7: Lab: Performance and Query Diagnostics

Use Query Diagnostics to find the steps and sources that take time
Table.Buffer and List.Buffer: when they help and when they hurt
Faster merges: trim columns before joining and choose the right join kind
Privacy levels and their effect on speed when combining sources
Lab: cut the refresh time of a sample file and measure before and after

Section 8: Lab: Incremental Refresh

How incremental refresh works with the RangeStart and RangeEnd parameters
Define a policy for how much history to keep and how much to refresh
The query folding requirement and testing in Power BI Desktop
What happens when you publish to the Power BI service, and the licence needed
Lab: set up incremental refresh for a multi-year sales table

Section 9: Power Query in the Power BI Service and Dataflows

Refreshing in the service: credentials, privacy levels and scheduled refresh
When an on-premises data gateway is needed and what to prepare
How Power Query differs across Power BI Desktop, Excel and dataflows
An overview of Dataflow Gen2 in Microsoft Fabric and sharing queries across reports
Use an AI assistant to explain and draft M code, and check the result before using it
Lab: publish a report and schedule a refresh in your own workspace

Section 10: Workshop: A Data Pipeline with Power Query

Brief: combine branch Excel files, an API and a database into one data set
Design staging queries, functions and parameters that are easy to maintain
Handle errors and add a data quality query
Check query folding and measure refresh time
Present and review the M code together

Schedule & training options

For individuals — public rounds

No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770

Join the waiting list Ask about rounds

For organisations — in-house / private

Tailor the content to your team’s tools and projects
Your dates, at your office or live online
Quotation with tax ID for procurement

Corporate training quote

Instructors

Samit KoyomFounder & CEO, IT Genius Engineering

Frequently asked questions

How does this course differ from Power Query in Microsoft Excel?

This course builds on Power Query in Microsoft Excel, which teaches click-based Power Query, combining files and M basics in Excel. This course works in Power BI and goes much deeper: writing M yourself, advanced functions, paged web APIs and JSON, error handling, query folding, Query Diagnostics, incremental refresh and refreshing in the Power BI service.

Do I need programming experience?

No, but you should already be comfortable with click-based Power Query. The course starts by reading the M code Power Query generates, then editing and writing it step by step. If you have written nested Excel formulas, you will adapt quickly.

Do I need a Power BI licence, and does the institute provide one?

The institute does not provide accounts or licences. Most labs use Power BI Desktop, which is free. Publishing and scheduling refresh in a shared workspace, including incremental refresh in the service, needs your own or your organisation's Power BI account, usually Power BI Pro or higher. If you do not have one, you watch the instructor's demonstration for those parts.

Do I need to install SQL Server?

Yes, ideally. SQL Server Developer Edition is free and lets you do the query folding and incremental refresh labs in full. An installation guide is sent before the training day. If you cannot install it, you can still complete every other topic and watch the demonstration for those parts.

Can I apply what I learn to Power Query in Excel?

Almost all of it. The M language, functions, combining files, APIs and error handling work in both Excel and Power BI. The parts specific to Power BI are incremental refresh, parts of Query Diagnostics and refreshing in the Power BI service.

Can I use AI to write M code?

Yes. The course shows how to use an AI assistant to explain and draft M code, but you need to read the code to check that it is correct, does not break query folding and does not send confidential data to outside services. AI features in Power BI need a supporting licence and change often.