Power Query and Power BI Combo Course

Install power query for excel, open the power query editor, trim extra spaces to clean data, and load results to a new sheet without altering the original.

Power Query interface to clean and transform data, combine multiple sources into a single data model, and perform operations like merge, append, split, pivot, and replace.

Learn to clean data with Power Query for pivot table analysis. Trim data in Power Query, then refresh pivot tables to reflect updated counts.

Format dates and values in Power Query by converting the date column and setting payments to currency, then load the updated table back into Excel.

Parse URLs in Power Query to extract category, state, and city into separate columns, and clean dirty data by splitting on the forward slash delimiter.

Split text fields in Power Query to extract barcode, product name, and size. Use leftmost and rightmost delimiter techniques, and pattern-based splits for the size unit.

Group by sales data in Power Query to calculate total sales by each salesperson and by sales region, using sum aggregations and advanced settings.

Explore unpivoting and pivoting columns in Power Query to transform data from column to row format, enabling month-wise sales analysis for each customer and easy pivot table reporting.

Pivot columns in Power Query to turn monthly sales data into separate month columns, using amount with a chosen aggregate function, then close and load the transformed data.

leverage Power Query to split comma-separated locations into columns, unpivot to list each company's locations in rows, trim spaces, extract location numbers, and prepare a pivot table to count locations.

Filter data in Power Query by John Doe and East Asia, convert dates to date only, extract year, and filter for 2022 and 2023; close and load and adjust columns.

Sort data by multiple levels in Power Query, first by salesperson and then by sales region, convert to date, apply a greater than or equal to 50,000 filter, and load.

Explore Power Query in Power BI to add columns, extract year and month from order date, split first names, compute invoice after tax, calculate tax percentage, and round results.

Combine Excel data files in Power Query by loading from a folder, extracting data from each workbook, and cleaning with fill down and promoted headers.

use Power Query and Power BI to import multiple workbooks from a folder, clean and split first and last names, and refresh to auto update pivots.

Extract form data into a Power Query table by importing multiple Excel forms, cleaning inconsistencies, and using index, modulo, and conditional columns to align first name, last name, and status.

Extract multiple criteria in Excel Power Query by filtering private events allowed to yes and square meters between 20,000 and 30,000, while cleaning headers and filling down owner names.

Learn to extract all worksheets from a single Excel file with Power Query, combine them into one table, and clean data by setting headers and removing extra headings.

Explore joining two tables in Power Query using the student ID as the common key to combine course data with student details into a single view, like a Vlookup.

Merge two tables in Power Query using joins to consolidate student information with enrollments, then create pivot tables to count courses per student.

Learn how to merge two tables using a full outer join in Power Query, ensuring all rows from both tables are combined, handling non-matching IDs, and cleaning data with nulls.

Use a right anti join in Power Query to identify students who need counseling by comparing two tables of class and student IDs.

Convert reports to a pivot table with Power Query by removing totals and repeated headings, fill owner names, then create a pivot table to count properties and average lot size.

Learn how to transform a single-column data set into a clean table with Power Query using the modulo function and pivot column to separate region, salesperson, and sales amount.

Extract file names from folders using Power Query, filter for Excel files, and load results into a sheet with a single column; refresh to update when back end files change.

Learn how to extract file names based on a user-selected folder in Power Query, create a drop-down for folder names, and dynamically refresh results in Excel.