Microsoft Power BI for Advanced Data Transformations

Name: Microsoft Power BI for Advanced Data Transformations
Rating: 4.4 (2254 reviews)

Learn how to clean, optimize, and model data tables to develop insightful, actionable insights in Microsoft Power BI

Created byEnterprise DNA, Sam McKay

Last updated 1/2025

English

German [Auto],English [Auto],

What you'll learn

Master advanced techniques in the Power BI query editor for optimized data handling.
Gain expertise in 'M' code and the advanced editor for efficient data transformation.
Learn to implement advanced data cleaning and data modeling strategies in Power BI.
Understand and execute effective data management for comprehensive data analytics.
Develop an analytical mindset to create correct relationships within your data model.
Implement organized and intuitive data model layouts for diverse data scenarios.

Course content

7 sections • 51 lectures • 4h 7m total length

Learning Tips From Enterprise DNA1:33
Microsoft Power BI user-interface updates to be aware of0:53
Course Overview - Let's get started!0:26
Resource - Data Transformations and Modeling in Power BI0:02
Intriguing Power Query Facts0:28
Mastering Best Practices: Query Editor & Data Model6:03
Explore best practices for the query editor, including why to use it, how to import and clean data there, and how to organize and optimize queries for a data model.
Why It Is Essential To Use The Query Editor in Every Project4:53
Discover why using the query editor is essential in every Power BI project. Learn to transform, clean, and optimize data before loading to the model.
A Practical Guide: Importing Data into the Query Editor7:07
Explore multiple data import options into the Power BI query editor, including text, folders, databases, and web sources, then use the advanced editor to transform and optimize data for modeling.
Unleashing Query Editor: Tips & Techniques8:14
Discover practical Power BI query editor tips, importing Excel tables, renaming and cleaning data, removing redundant columns, and organizing lookup and fact tables into a concise data model.
Demystifying M Code: A Comprehensive Understanding10:47
Explore m code in Power BI's advanced editor, learn how transformations are generated and applied sequentially, including renaming, splitting, removing columns, changing types, and finalizing the query.
Troubleshooting in Power BI: A How-to Guide8:19
diagnose and fix common Power BI query editor errors caused by changes to table structure, column names, or data types, using the advanced editor and M code.
Crafting Order: Effective Organization within the Query Editor4:11
Organize your Power BI query editor with intuitive groups and folders, managing staging queries, supporting tables, parameters, and measure groups to structure a scalable data model.
Introduction - Review1:13
Your Feedback Matters!0:40

Intriguing Power Query Facts0:26
Column Transformations: Practical Examples9:02
Master column transformations in the Power BI query editor, from duplicating and splitting to trim and case changes, to automate clean, reusable data and easy refreshes.
Row Transformations: Hands-On Techniques6:53
Explore row transformations in Power BI, apply first row as headers, transpose, reverse rows, and add index columns; count rows and remove top rows and duplicates to clean data.
Data Cleansing: How to Filter Unrequired Data from Tables4:18
Filter data sets in Power BI to focus on export sales, international sales, or dates, reducing rows before modeling. Pre-filter in the query editor to optimize your model.
Date Table Creation & Modification: An Interactive Approach7:35
Create a detailed date table in Power BI using M code in a blank query, enabling multi-dimensional date filtering with day of week, quarter, and calendar fields.
Data transformation examples - Review0:51
Active Recall - Data Preparation and Cleaning0:57

Power Query: Did You Know?0:34
Staging Queries: A Walkthrough5:28
Explore staging queries to consolidate year-specific sales tables into a single, optimized data model by using a staging area, grouping queries, and merging or appending in the query editor.
Amplify Your Data: Appending Queries Explained5:21
Comprehensive Guide to Merging or Joining Queries8:48
Merge channel details staging query into the sales fact table with a left outer join to integrate channel dimensions. Review applied steps to optimize Power BI data model.
Turning Data Around: Unpivoting Tables for Efficiency9:06
Learn how to unpivot columns to consolidate exchange rate data into a single column, then create lookup tables and staging queries to build efficient Power BI models.
Extracting Insights: Creating Columns from Examples5:49
Explore columns from examples in Power BI's query editor, a machine learning feature that quickly merges columns and derives month names from dates for your Power BI data.
Custom & Conditional Columns: Building for Specific Needs8:42
Learn how to use custom and conditional columns in Power BI, compare alternatives like column from examples, and create practical indexes for currency data.
Parameterizing Your Data: Creation & Utilization8:33
Explore parameterized data transformations in Power BI using the query editor's parameters to filter across the model with a currency parameter for USD, GBP, CAD, and yen.
Boosting Functionality: Custom Functions in Power BI10:13
Explore custom functions in the power bi query editor by turning parameterized queries into reusable code, using channels and years to filter and invoke a usd export sales table.
Synthesis: Merging Advanced Transformation Techniques5:24
Explore advanced data transformations in Power BI through the query editor, staging queries, and applied steps, shaping a data model with parameters and custom functions for scalable reports.
Advanced transformation and querying techniques - Review1:26
Power Query: Beyond Basics0:36

Architecting Insights: Power BI Data Modeling Facts0:32
Data Modeling Goals: What We Aim to Achieve3:36
learn how to structure a Power BI data model by organizing fact and lookup tables, denormalizing lookups for performance, and linking them with relationships for scalable analytics.
Crafting Efficient Data Models: Best Practices5:47
Visualize a waterfall of filters in your data model, placing lookup tables at the top and fact tables at the bottom to enable cross-table, one to many filtering.
Tackling Complexity: Setting Up Intricate Models11:25
Build advanced Power BI models with budgeting data, creating a 2016 regional budgets table from 2015 results, using lookup tables and one-to-many relationships for waterfall filtering.
Handling Absent Lookup Tables: Alternate Approaches7:48
Discover how to handle absent lookup tables by creating on-the-fly lookup tables from fact data (e.g., warehouses, channel info) and linking them to your model to add dimensions and filters.
Constructing Measure Tables/Groups for Better Insights7:27
Master creating DAX measures in Power BI and organizing them into dedicated measure tables or groups, enabling scalable calculations like time comparisons and cumulative totals for clearer insights.
Leveraging Intermediary Tables in Your Model8:40
Supporting Tables: A Vital Component of Data Modeling9:13
Scenario Analysis with 'What If' Parameters3:44
Explore setting up what-if parameter scenarios in Power BI to analyze pricing impact. Create a pricing scenario, use a supporting table, and integrate the measure into core model calculations.
Enhancing Lookup Tables: Adding Dimensions7:14
Learn to elevate Power BI dashboards by enhancing lookup tables to create new dimensions, using calculated columns, switch logic, and context transition to filter facts and budgets across relationships.
Architecting Multi-Layered Models: An Advanced Approach4:18
Explore multi-layered models in Power BI, layering lookup, intermediary, and fact tables with measure and supporting tables, and implement DAX logic to visualize filters and relationships in your data model.
Designing advanced data models - Review1:34

Blueprints of Insight - Exploring Power BI Data Modeling0:33
Active vs Inactive Relationships: Deciphering the Differences7:24
Learn how to handle multiple dates in a fact table using one date table, and use active and inactive relationships with DAX to analyze by order date or ship date.
Elevate Your Power BI Experience: Model Optimization Techniques4:52
Arrange Power BI models in a clear, multi-layered layout to reveal the waterfall flow of filters. Prioritize one-to-many relationships and simplify with the query editor for a more intuitive model.
Shrink Your File, Not Your Data: Reducing Model File Size6:36
Learn to shrink Power BI model file size by cleaning fact tables, removing unused columns, splitting similar columns into lookup tables, and using query-level parameters to filter data before loading.
Optimising your models - Review0:39

Requirements

Participants need a basic understanding of Power BI and DAX, including familiarity with creating simple reports and basic data manipulation.
An active Power BI account is a prerequisite, as it will serve as the platform for all practical exercises and hands-on training throughout the course.
A fundamental understanding of data concepts and some experience in data analysis is required to effectively grasp the advanced concepts taught in the course.
Lastly, an enthusiasm for learning advanced data transformation and modeling techniques will greatly enhance the learning experience and outcomes.

Description

Deepen your understanding of data transformation and optimization techniques with our advanced Microsoft Power BI course.

Over 4 hours of high-quality video content, we break down the complexities of the Power BI query editor, illustrating its importance and impact on effective reporting solutions. From beginners to advanced users, this course broadens your comprehension of intermediate to advanced techniques, empowering you to adeptly clean, optimize, and connect your raw data tables into an efficient analytical model. We demystify the use of DAX formulas to extract accurate results that align with your analytical queries, thereby ensuring a rich, data-driven decision-making process.

We kickstart the course with best practice techniques for the query editor and proceed to detailed instructions on manipulating 'M' code and the advanced editor. Comprehensive lessons on row and column query transformation options lay a solid foundation for understanding complex data structures. The course then navigates through advanced data cleaning and transformation techniques, with hands-on examples demonstrating how to query multiple tables.

As we progress, you'll learn strategic ways to think about and manage your data model, mastering techniques applicable to any data scenario. By course end, you will not only be proficient in advanced data modeling techniques, but also understand how to navigate complex modeling scenarios and situations, and effectively organize your models.

The course comes bundled with a demo dataset and model, offering a practical playground for you to experiment with advanced querying and data modeling techniques. As you embark on this course, expect an enriching journey into the advanced realms of Microsoft Power BI, transforming the way you perceive and use your data.

Who this course is for:

Power BI users looking to enhance their skills in advanced data transformations.
Data Analysts aiming to improve their data modeling and transformation techniques.
Reporting Managers seeking to create more effective and data-driven reports.
Business Intelligence enthusiasts who want to deepen their understanding of Power BI.
Anyone interested in mastering advanced concepts in Power BI for career advancement.

Microsoft Power BI for Advanced Data Transformations

What you'll learn

Explore related topics

Course content

Introduction14 lectures • 55min

Data transformation examples7 lectures • 30min

Advanced transformation and querying techniques12 lectures • 1hr 10min

Designing advanced data models12 lectures • 1hr 11min

Optimising your models5 lectures • 20min

Course Assignments - Advanced Data Transformations0

Additional Resources - Complimentary guides for your data career1 lecture • 1min

Requirements

Description

Who this course is for: