Stepping beyond the Power Query User Interface

Power Query transforms unstructured data from PDFs, web sources, TXT, and SQL into structured tables, automating transformations like a macro recorder for fast, repeatable loading in Excel and Power BI.

Navigate the power query user interface in Excel and Power BI, access get data and transform data, and explore the editor, queries, previews, and applied steps.

Power BI broadens Power Query beyond Excel with over 100 connectors across six categories, adds a web connector for scraping, and enables live or direct query plus AI insights.

Connect to web data sources with Power Query, scraping structured and unstructured websites to transform and load data into a Power BI model, and refresh to fetch new data.

Learn to connect to a SQL server for a retail data warehouse, compare import and direct query, and understand query folding, its benefits, and its limitations.

Import and combine multiple text files from a folder into a single table using Power Query, then preview, clean, and load to the data model with compression benefits.

Connect to an Excel table in Power BI, not a sheet, to keep scripts stable. Learn how Power Query reads tables, trims excess columns, and handles refresh while preserving structure.

Connect to data in the current Excel workbook by converting a range to a proper table, then load via Power Query and add to the data model for pivot analysis.

Connect to data with Power Query, grab and transform data from about 200 connectors, structure it properly, and load into a data model, Excel sheet, or Power Pivot.

Explore the most important data transformations in Power Query and learn techniques to clean Excel data.

Explore data profiling and data quality in Power Query by examining call quality, distribution, column profile metrics (std dev, average, min, max, zero values) on first 1000 rows, with caution.

Master column operations in Power Query, including renaming, splitting, merging, rearranging, removing, adding conditional and index columns, and creating a date from day, month, and year.

Explore column transformations by adjusting data types and applying case changes, filtering, and list conversions, with practical guidance on handling text, date, and numeric errors.

Define column types before filtering, then apply per-column filters using the appropriate data type—text, date, or number—to remove totals from roll values and clean the data model.

Apply change type using locale to correctly convert numbers and dates when scraping data, using German locale to treat comma as decimal and prevent misinterpretation across environments.

Explore how to add and configure conditional columns in Power Query, using the et column tab to create per-row calculations and categorize units sold as retail or wholesale.

Explore conditional columns in Power Query, selecting outputs from specific columns, and building complex and nested rules for sales type and payment conditions.

Learn to add and customize index columns in Power Query, including starting points and stepped sequences, and create custom columns like total revenue with row-by-row calculations.

Create custom columns by writing logic in the column space, using all columns to merge store code initials, product initials, and year from date; Power Query auto-generates the formula.

Explore how power query handles null values in arithmetic with a custom column to compute total stock. Replace nulls with zeros to avoid null propagation and get correct totals.

Create and manage lists in Power Query from a data table, turning single columns into lists, removing duplicates, and exploring different ways to generate lists via the user interface.

Document your Power Query steps by adding descriptions in the Applied Steps pane and using the step commenting option to explain custom columns and calculations.

Learn how to pivot and unpivot data in Power Query to transform a wide table into clean date, product, and final values columns.

Explore a real pivot/unpivot example in Power Query, turning a year-long table into date-driven columns and separate fact columns, then prepare data types for loading into a data model.

Group by descriptive columns to create higher-granularity results and sum the value column. Use Power Query to fold the query to SQL Server and verify the view native query.

Explore lab 1 solution that demonstrates creating dim product within the Power Query environment, as part of stepping beyond the Power Query user interface.

Explore how to create a fact table in a lab setting. Move beyond the Power Query user interface with this lab solution.

Explore how to merge queries in Power Query, using a left outer join on a key column to combine product and sales tables, then expand and refine fields.

Learn how to use left outer merge in Power Query without creating nulls or multiplied sales, by expanding table variables carefully and removing duplicates to ensure accurate results.

Explore the types of merge in Power Query, including inner, left outer, right outer, and full outer joins, using the product column as key and expanding results.

Explore the expand and aggregate merge options in Power Query, illustrating how expand preserves granularity while aggregate computes totals, avoiding duplicates and clarifying outcomes with continent and country data.

Learn how to use fuzzy merge in Power Query to combine keys that aren’t identical, adjust similarity thresholds, and manage risks of false matches.

Append queries across three data sources in Power Query to create a unified final table, cleaning and aligning columns, and addressing case-sensitive region names to ensure correct merging and loading.

Explore the query dependencies view to visualize lineage between queries, their Excel and web sources, and operations, including duplicates and references, plus how appending and loading status affect performance.

Explore data privacy in Power Query by merging a SQL database with an Excel file. See how privacy levels protect sensitive data and when to avoid exposing data through merges.

Learn how to manage data credentials in power query via data source settings, edit permissions, and credential types (Windows, database, Microsoft account), securing credentials with privacy levels and encrypted connections.

Step beyond the Power Query user interface by exploring lab 2 solution that combines Virginia and Colorado sales data.

Explore a lab solution for expanding the dim product within the Power Query user interface, illustrating practical data shaping and dimensional expansion techniques.

Move beyond the Power Query user interface by embracing the M language, crafting custom functions, parameters, and dynamic scripts to automate daily data manipulation.

Explore Power Query M language fundamentals, including the let and in syntax, step-by-step transformation concepts, and how transpose, promote headers, and advanced editor reveal query logic.

Explore power query objects by mastering primitive values, function values, and structured values such as lists, records, and tables. Drill down primitives, and learn to invoke functions.

Explore Power Query lists, a single-column data structure with limited transformations, learn to create lists from columns or from scratch, and access nested items with zero-based indexes and List.Dates.

Learn how Power Query uses records as structured data alongside lists and tables, and how to create and extract records for custom functions and metadata.

Power Query tables as the core structured object, and learn to create and convert tables from lists and records using curly braces syntax and drill into rows and columns.

Mix and combine steps to create complex Power Query queries using let and in syntax, reference previous steps, and store variables for advanced features beyond the regular Power Query interface.

Explore how to set a variable inside a Power Query by extracting a top row as a record, storing it as details, and expanding it for the final table.

Learn how to dynamically expand Power Query table columns by using lists to replace hard-coded merges, enabling automatic inclusion of new columns on refresh.

Expand dynamic column names across multiple tables in Power Query by building a combined list of distinct column names with Table.ColumnNames, and expand columns using a nested let environment.

Discover how to build nested Power Query logic using expressions and variables inside let and in environments, mastering inner and outer variable access.

Master error handling in Power Query by distinguishing step level and row level errors, and using filters to keep only error rows for quick diagnosis.

Learn to use the try expression in Power Query to gracefully handle errors during query merging, provide custom error messages with error.record, and adapt outcomes with try otherwise.

Explore how the each keyword in Power Query iterates over table rows, enabling a custom column that sums stock values and handles nulls by replacing them with zero.

Discover how to view and explore Power Query functions and their documentation in the editor by converting a shared record to a table and filtering by name.

Use Excel created parameters to filter Power Query data and dynamically switch between Croatia and Germany folders by injecting region and path values from a parameter table.

Explore parameters in Power Query and Power BI, create and link parameters to filter regions, use lists or queries, and parametrize data sources for cross-country files.

Explore how Power Query functions automate repetitive data transformations by building custom functions, feeding them with tables and lists, and handling errors to apply changes across multiple data sources.

Learn to transform a Power Query query into an M function and apply it to February data, using a January structure as a guide and invoking the function.

Learn to automate multi-file data prep in Power Query by creating a reusable M function and invoking it across a folder of Excel files, transforming and loading clean data.

Learn to reuse M code by copying and invoking functions across Power Query projects, creating dynamic date tables with end date parameters and new queries.

Learn how the table buffer function boosts Power Query performance, and master nested iterations and the each function for partial matches between category and product data.