Master Excel Power Query & Pivot Tables – Complete Guide

Power Query in Excel helps you discover, connect to sources like Excel, CSV, web pages, and databases, then transform, clean, split, pivot, and merge data for reporting and sharing.

Explore how Power Query transforms messy customer data into clean, independent columns, enabling efficient sorting, filtering, and pivot table analysis.

Discover how to reuse Power Query steps by duplicating the original data, changing the source to new data, and quickly loading the transformed results into the worksheet.

Navigate the Power Query interface and launch the Power Query Editor to explore the Home, Transform, and Add Column tabs for data sources and queries, and adjust view options.

Learn to create a live Power Query connection to an external Excel file, import and transform data, load it in your workbook, and refresh to reflect master data updates.

Learn to import Excel table data with Power Query, create a live connection, and load it to a new worksheet for refreshing updates and building dashboards with pivot tables.

extract text data using Excel Power Query from text files, clean up header columns, transform comma-delimited data, and load as a live connection that refreshes with source changes.

Combine data from a folder using Power Query to merge five state files in the order information folder into a single data source, enabling pivot tables and refresh.

Connect to a web page with Power Query, pull table data into Excel, transform and clean it, and load it for a refreshable link to the source.

Explore Power Query editing in Excel to transform and clean a customer data query by standardizing headers, applying proper case, normalizing active flags, converting text dates, and unwrapping addresses.

Format the data as a table in Power Query, then edit headers by inserting spaces in the Power Query editor, producing readable titles like contact name and customer date.

Learn to clean and convert column data types in Power Query, turning booleans from minus one and zero into true or false, and resolve date formats using locale for reporting.

Split the contact name column into first and last names in Power Query by delimiter, using space as the separator and leftmost or each occurrence, then rename the resulting columns.

Practice splitting columns with power query using delimiter and by character count to create clean data, then partition sku into product code, supplier id, and warehouse id.

Use the Power Query Editor to split the sku into three-character chunks for product id, supplier id, and warehouse, and split name by comma into last name and first name.

Capitalize each word in the company name and address using Power Query's text transform to clean lowercase data in the customer info new query.

Sort data in Power Query by a column header, such as state, choosing ascending or descending order, while the source data remains unchanged and can refresh for updates.

master multi-level sorting in power query by sorting the state name descending and the last name ascending within each group, using primary and secondary sort indicators.

Filter records in Power Query using city and date fields with text and date filters, and export the result to a dataset for pivot tables while preserving the original data.

Eliminate duplicate records in Power Query by first keeping duplicates to review, then removing duplicates to reduce 91 rows to 87, and refreshing the query to reflect source data changes.

Apply data cleaning techniques in Power Query to tidy a stock list by sorting, filtering, and adjusting headers and data types, then share your cleaned results.

Explore Power Query load settings, including close and load, close and load two, and only create connection, then load data into worksheets or the data model for Power Pivot.

Load the query into the data model with the close and load two option and add it to data model, then manage in PowerPivot to create calculated columns and KPIs.

Explore Power Query load settings to customize defaults for loading queries, choosing between a new worksheet, the data model, or multiple queries, and how to adjust these options.

Explore the refresh options in power query to keep data up to date from centralized sources, and learn when to use refresh, refresh all, and cancel refresh.

Learn how to delete a query in Power Query, removing the connection and preventing refresh, by right-clicking in the workbook queries panel and confirming deletion.

Activate the PowerPivot add-in in Excel to reveal the PowerPivot tab on the ribbon, by going to file options, add-ins, and enabling Microsoft Office PowerPivot for Excel.

Learn to clean and reshape poorly formatted region revenue data with Power Query using transpose, use first row as header, and fill down to create a pivot-ready table.

Learn to use Power Query's pivot column to produce category wise inventory summaries by discontinued status, removing duplicates, and summing inventory value without pivot tables.

Pivot columns in Power Query to create separate freight amount columns by ship through and year, using close and load to manage connections and optional worksheet outputs.

Unpivot month columns in Power Query to turn headers into rows, creating a month and revenue dataset that can be sorted and filtered.

practice unpivot columns in excel power query, building on pivot concepts while applying to a simple revenue dataset; complete the assignment and share results on the discussion board.

Learn how to duplicate a query in Microsoft Excel Power Query, rename and tweak the duplicate for separate reports, and keep it connected to the original data source.

Learn how to group data in Power Query using the basic group by option, create a monthly revenue summary, and format the revenue as currency using locale settings.

Learn to merge two tables on the employee ID and append two lists in Power Query using Excel, building a combined employee and order dataset.

Practice duplicating a query in Power Query, group data by month or city to summarize monthly revenue and average revenue, then load results into an existing worksheet.

Create custom and calculated columns in Excel Power Query to perform date calculations, conditional mappings, and freight pricing for order data such as order date, required date, and ship date.

Create a conditional column in Power Query to map ship through values to shipper names like national transporters, eastern packers, and Speedo Express.

Compute date differences in Power Query to determine processing time between order date and ship date, highlighting proper column order and using subtract dates and duration.days.

Rename the name column to order day, apply a Sunday premium with 1.15 multiplier via a conditional column, then calculate revised freight charges with a custom column.

Learn to add an index column in Power Query to number records, rename it to order index, and use it to revert to the original sort.

Practice creating custom and calculated columns in Power Query. Work with date and conditional calculations on an order table, including order date, ship date, taxes, shipping fee, and payment type.

Connect Excel Power Query to an external data source, clean and transform the data, load it into a worksheet, and build a pivot table for reporting orders.

Build an Excel pivot table from a Power Query data source, using the field list to group by ship state and count orders for quick insights.

Learn to group date fields in Excel pivot tables by year, quarter, or month, using the group field to analyze orders and city counts.

Enrich Power Query source data by adding a calculated column that derives the day name from order dates, then use it in a pivot table to count orders by day.

Learn to switch a pivot table's value field from sum to average for each day of the week, and apply currency formatting for clean shipping fee analysis.

Turn a pivot table into a pie pivot chart to visualize the average shipping fee by day of the week, using the pivot chart tool in Excel Power Query.

Add a slicer to the pivot table to filter data by order date, enabling interactive, multi-day selection under the chart.

Learn to add and use a timeline slicer in pivot tables with order date, ship date, and paid date, filtering by months, years, quarters, or days for multi-dimensional date analysis.

Refresh the Power Query and PivotTable to pull updates from the external data source, then watch the dashboard reflect the newly added records and the updated average shipping fee.

Practice building a pivot table dashboard from an Excel Power Query data source by downloading the sample files, cleaning data, and loading into a worksheet to analyze order details.