In this chapter, you’ll delve into Power Query's data integration capabilities. You’ll master the art of appending queries to combine datasets seamlessly. You’ll also learn to merge datasets together, harnessing various join types to render datasets in multiple ways.  

Appending and merging queries

Options

Append

Merge

Using Append vs. Merge

Combining queries with Power Query

Appending queries

Merging queries with left outer join

Merging queries with inner join

Combining Queries in Power Query

It’s all about customization in this chapter. You’ll learn the fundamentals of M language syntax, and how it underpins every transformation step in query development. You’ll leverage M to create custom conditional columns using logical operators to suit numerous scenarios. You’ll also delve into M table transformation expressions to create custom grouped index columns, enhancing your proficiency in advanced query design.

Custom columns

Conditional logic with M

Custom columns in Power Query with M

Custom conditional columns

Advanced conditional columns

Calculating early warning system scores

Grouped index columns

In this final chapter, you’ll delve into the Power Query Advanced Editor, unraveling M-Language syntax for code modification and debugging. You’ll explore M's built-in functions, and master best practices such as query grouping and dynamic filtering with parameters. Lastly, you’ll elevate your skills by crafting custom M formula language functions, unlocking advanced query design capabilities

M Formula Language

Power Query Advanced Editor

M for Power Query in Excel

Built-in M functions

Query parameters

Debugging the advanced editor

Custom functions

Congratulations!

Exercises and Datasets

Building on your foundational Power Query in Excel knowledge, this intermediate course takes you to the next level of data transformation mastery. Discover advanced data integration techniques, including appending and merging datasets with various join types. Delve into M language syntax for creating custom conditional columns and harness advanced table transformations. Unlock the Power Query Advanced Editor, exploring M's built-in functions, mastering best practices like query grouping, and dynamically adjusting filter criteria with parameters. Elevate your skills by crafting custom M formula language functions, unleashing advanced query design capabilities that will empower you to extract deeper insights from your data.

Introduction to Power Query in Excel

Data Preparation in Excel

Data Analysis in Excel

Building on your foundational Power Query in Excel knowledge, this intermediate course takes you to the next level of data transformation mastery

Intermediate Power Query in Excel

Data Analysis with Excel Power Tools

Combine sales data from different regions or branches to consolidate and analyze overall sales performance across the organization.

Combining patient records from different medical facilities to create a comprehensive patient dataset.

Combining monthly employee timesheets to create a complete dataset for payroll processing and workforce analytics.

Combine patient record demographic data with their medical procedure outcomes to create a comprehensive patient history.

Combine a transaction table with actual spending by category with a budget table to create a consolidated financial report. 

Combining sales data with customer information based on customer IDs to analyze purchasing behavior and customer demographics.

Using Append vs. Merge

Intermediate Power Query in Excel

Latihan interaktif praktis