Master Excel Power Query - M Language and Practical examples

Power Query basics, its Excel integration across versions, and its use for extracting, cleaning, transforming, and modeling data for self-service BI.

Discover four ways to import data from an Excel file into Power Query - table, named range, dynamic range, and workbook sheets - and load the data.

Learn how to import data into Power Query from text files, CSV, Excel, and Access, select sheets or fixed/dynamic ranges, and apply basic transformations before loading into Excel.

Load data from an Excel workbook into Power Query, and choose to load to a worksheet, data model, or create a connection, then refresh queries when the source changes.

Master Excel Power Query column transformation by renaming, adding, removing, splitting, and merging columns, and learn to sort, transpose, and unpivot data with recorded steps for efficient data shaping.

Import data into power query and apply row transformations, including promoting headers, removing top and bottom rows, filtering, removing duplicates, replacing errors, and using group by to summarize sales.

Explore Power Query text transformations, including built-in and conditional options, with upper, lower, prefix, suffix, splitting by delimiter, merging, and left-right length extraction to create new columns.

Explore numbers transformations in Power Query using M language by applying min, sum, multiplication, rounding, and even/odd checks on the amount column, illustrating how to build sequential transformation steps.

Learn to perform date transformations in Power Query using M language, including extracting the year, month, quarter, start and end of periods, and days in month.

Explore conditional transformations in Power Query by adding a conditional column with multiple rules, mapping product names to 'cycle' or 'others', and comparing columns to output yes or no.

Append data from three tables within the same Excel file using a blank query in Power Query, loading with original column as a prefix.

Master excel power query concepts by learning how to append, duplicate, and reference multiple queries, ensure identical column headers, and consolidate monthly files using create connections and combined queries.

Import and consolidate all files in a folder with Power Query, appending diverse file types into one dataset. Automate updates as new files arrive, with extension filtering and origin tracking.

Learn to import data from multiple Excel files in a folder, combine multiple sheets into a single table using Power Query, and clean or filter columns for usable analysis.

Learn to generate a dynamic list of all files in a folder using Power Query, including file names, types, and attributes, with refreshable, subfolder support.

update a Power Query data source by changing the file path when shared or moved files cause errors; browse or paste the new path and refresh the query.

Explore how Power Query's merge queries enable multi-column lookups and joins, including full outer, inner, left outer, right outer, anti and cross joins, with notes on limitations and workarounds.

Explore how to merge two tables in Power Query using full outer, left, and right joins, aligning by product key and category to reveal matched and unmatched rows.

Explore cross joins and the Cartesian product in Power Query by combining two tables with no matching keys to produce all year-by-month combinations, using custom columns and references.

Explore M building blocks in Power Query, including case sensitivity, single-column data types, zero-based indexing, lack of intellisense, and text, date, and conditional functions.

Explore text functions in Power Query with practical examples of transformation and extraction, including uppercase, lowercase, len, text.start, and text.range, using a customer data set.

Explore date functions in power query to convert to date, extract year, month, day, and quarter, and compute start or end of periods using ribbon or custom column syntax.

Explore conditional functions in Power Query, including if, and, or, and apply them with custom columns to transform priority ratings and handle mixed numeric and text data.

Learn to use Power Query to transform messy financial statements into analyzable columns, including transposing, promoting headers, and splitting data, so you can perform descriptive statistics and feed dashboards.

Learn to clean and shape payroll data in Power Query by creating a custom conditional column, renaming and merging fields, and applying transforms to produce a structured monthly payroll view.

Shape an address book in Power Query by adding an index, building a custom column, and renaming and reordering fields to consolidate six rows per person into a clean dataset.

Master Excel Power Query demonstrates applying multiple filters on a single column, merging queries, and extracting GDP data for selected countries across specific years.

Transform rows into a columnar data set in Excel Power Query using M language, adding a custom index-based column to separate even positions, and loading the results into a table.

Explore Power Query to look up discount rates from multiple tables, apply rules, and compute net discounts by merging tables and creating custom columns.

Learn to clean and reshape invoice data with Power Query by transposing headers, unpivoting columns, merging columns, promoting headers, and loading a properly formatted table.

Learn to use Power Query in Excel to pull from multiple tables, transform data, and perform sales analysis—total sales, commissions, and statistics by region, product, and date.