Power Query: Clean & Transform data with PowerBI Power Query

Learn how to combine data in Power Query with append and merge, adding rows or columns from multiple tables to form a single data source, a core data transformation.

Combine three worksheets into a single data source using append queries. Append as new creates a unified query from consumer, corporate, and home office with seven columns and 18 rows.

Learn how append queries in Power Query merge similarly structured data from multiple sources into one data set, aligning headers like segment and sales despite column order differences.

Learn how to use merge queries in Power Query to import state and region from the cities table by matching city columns with the transactions table, then expand the results.

Connect two data tables in Power Query and merge them using multiple columns as keys: year, quarter, and company, then expand to retrieve marketing costs.

Explore how Power Query merges two queries, define join kinds, and show how results depend on which side is prioritized and whether matches occur, including matched and unmatched features.

Explore merging transactions and cities data in Power Query using left outer, inner, right outer, and full outer joins to pull in state and region while handling missing values.

Learn how to merge two data tables in Power Query by keying on student names, add maths, English, and Biology scores, and avoid duplicate rows by ensuring unique keys.

Learn how to transpose data in Power Query, flipping rows into columns, and apply first row as headers to shape datasets.

Learn how to transpose data in Power Query without turning the first row into a header, by removing promoted headers and change type steps and reapplying transpose.

Learn how unpivoting columns in Power Query converts header values into a single months column with corresponding values, and compare this with transposing and pivoting.

Explore unpivoting columns in Power Query when a column contains null values. See how nulls disappear in the pivot results and how the apply steps before pivot influence the outcome.

Learn to unpivot columns in Power Query by converting the first row into headers and then unpivoting other columns to turn headers into attribute values.

Pivot a metric column in Power Query to create separate columns such as sales, quantity, discount, and profit, and aggregate values by sum for each order.

Learn to group by in Power Query to summarize data by customer name or multiple columns. Use basic and advanced grouping and aggregate total sales, total quantity, or average discount.

Learn how to add and manage new columns in Power Query, including duplicating columns, creating index columns, and working with general and data type specific, conditional, and custom column options.

Learn to add new columns from a text column in Power Query using the add column tab, including upper case, trim, and extracting the last value after the final hyphen.

Derive new columns from number and date columns in Power Query, including price per unit by dividing sales by quantity, renaming results, and extracting year or month names from dates.

Create a conditional column in Power Query to classify students by grade points into pass, fail, or advice to withdraw, using rules: <2.0 withdraw, <2.5 fail, else pass.

Create a conditional column called promotion status in Power Query, deriving values from next level, current level, or advised to withdraw based on the student grade.

Learn to create custom columns in Power Query by writing your own M expressions, refer to existing columns with square brackets, and build conditional columns using if statements.

Explore creating a custom column based on multiple conditions, such as sales over 1500 and quantity over five, to apply a 20% discount, and compare with conditional column use.

Use column from example in Power Query to create a new column by applying the input pattern; Power Query infers results from the first 1000 rows and needs clear patterns.

Apply power query transformations to fill nulls, replace values, merge and split columns, create full name from first and last names, and split location IDs into postal code and city.

Highlight all columns using Ctrl+A to reveal duplicates with keep duplicates, then remove duplicates from the home tab or right-click header to delete duplicate records in Power Query.

Learn to diagnose and fix cell errors in Power Query by distinguishing errors from mistakes, handling divide-by-zero, text-to-number, and date format issues through replace values and data type adjustments.

Identify and audit cell errors in Power Query by using keep errors, table select errors, and a try expression to reveal error reasons and messages across columns.

Identify what is wrong in dirty data and use Power Query to clean and transform it, selecting the right tools to handle six sample cases.

Learn to clean and transform messy sales data in Power Query by transposing rows, unpivoting columns, filling down, and extracting date parts to align ship mode, segments, and order dates.

Learn to clean and transform messy sales data in Power Query for Power BI by reshaping ship mode and segment, transposing data, merging columns, and splitting order IDs and dates.

See how to clean jumbled customer data in Power Query by using add column and extract text between delimiters to separate name, address, age, and gender into clean columns.

Separate numbers from text in a single column using Power Query, extracting quantity and units with column from examples and examining the generated M code.

Clean a dirty data set in power query by splitting merged categories and amounts with a pipe delimiter, duplicating queries, and merging them back with index-based alignment, then trim spaces.

Power Query demonstrates cleaning a multi-level account dataset by separating a single column into accounts type, category, and subcategory; removing empty rows and structuring headers for a clean dataset.