Microsoft Excel - Data Analytics Power Query and PivotTables

Explore how Power Query connects to diverse data sources in Excel and transforms data with built-in tools. Merge datasets and share clean queries to streamline reporting and analysis.

Explore how Excel Power Query transforms messy customer data into a clean, analysis-ready dataset for sorting, filtering, and pivot tables, demonstrated with an example file.

Reapply Power Query steps to quickly transform new data into a clean table, duplicating queries, updating the source, and loading ready-to-use results for analysis and pivot tables.

Explore the Power Query interface and launch the Power Query Editor to get data, navigate the Home and Transform tabs, and inspect the Queries panel.

Learn how to import data from an Excel table using Power Query, create a live connection, and load a transformed subset into a new sheet for dashboards and pivot tables.

Import a text file with Power Query, clean and rename columns, convert date-time fields to dates, remove unnecessary data, and load a live connection into Excel for easy refresh.

Import data from a Microsoft Access database in Excel with Power Query, selecting tables or queries, previewing and refreshing records, and loading into a worksheet for analysis.

Combine a folder of text files into a single Power Query data source, clean line breaks, and load into Excel for pivot tables and charts.

Pull data from web pages into Excel using Power Query, identify structured tables, transform data, remove unwanted columns, and load a refreshable query into a worksheet.

Explore Excel's Power Query tools to transform and clean data in a sample Power Query document, fixing headers, case, boolean values, dates, and wrapped addresses.

Split the contact name column in Power Query by delimiter to separate last name and first name into two columns, using a comma and rename them.

Practice splitting power query columns to define data pieces: split by number for SKU into product code and supplier ID, then split by delimiter for names, then close and load.

Demonstrates splitting Power Query columns by characters and by delimiter in Excel, renaming outputs, formatting currency, and loading the new data sheet with quick tips.

Apply text transformations in Power Query to clean text data by capitalizing each word in the company name and address, using the text-transform features and quick edit paths.

Sort data in Power Query by a column such as country or contact title, ascending or descending, while the entire list remains uniform and the source data stays unchanged.

Perform multi-level sorting in Power Query by first sorting contact titles to create groups, then apply a secondary ascending sort by last names within each group.

Filter records in Power Query to extract data such as country groups or date ranges, using text, boolean, and date filters while preserving the original data for pivot tables.

Master removing duplicates in Power Query by inspecting records, using keep duplicates to identify them, then applying remove duplicates and refreshing the connected data for clean lists.

Use Excel Power Query to clean year data by filling down the year values, replacing merged cells for reliable sorting, filtering, and analysis.

Discover how to load Power Query data into Excel by choosing load options, including table, worksheet, pivot table, pivot chart, and data model, and managing queries and connections.

Demonstrates loading a Power Query query into the data model using close and load two, then managing it in Power Pivot to create calculations, columns, and KPIs.

Learn how Power Query sets default load behavior, choosing between a worksheet or data model with Power Pivot, and customize defaults via query options for single or multiple queries.

Refresh Power Query data from external sources and a master document using refresh options, including refresh all, refresh per query, and background refresh settings.

Practice using the pivot column command in Power Query to summarize data by ship via and year, creating new columns and aggregating freight amounts.

Learn how to use Excel Power Query to unpivot columns, turning month headers into a single month column with corresponding sales values, enabling easier sorting and filtering.

Practice unpivoting columns to transform monthly sales data in power query, create and rename the unpivoted sheet, and prepare for more advanced pivot and unpivot scenarios in data analytics.

Duplicate a Power Query in Excel to preserve the original while creating a new, connected version of the yearly sales unpivot for further analysis.

Group and summarize data in Power Query by selecting a grouping column, performing basic and advanced grouping, and calculating monthly sales totals.

Master advanced grouping in Power Query by applying multiple groupings and summary columns, such as month and product, with aggregations like sum, count, min, and max.

Create calculated, custom, and conditional columns in Excel Power Query to analyze order, required, and shipped dates; compute date differences; and map ship via codes to names.

Create a power query calculated column to measure days between order date and ship date using subtract days. Select the two date columns in the correct order to avoid negatives.

Use Power Query to calculate days between order and ship dates, then extract name of day from order dates and pivot by day to reveal busy days for production planning.

Create a calculated column to identify Friday orders, apply a 10% freight premium, then build a custom column to compute the new freight amount in Power Query.

Open the exercise file in Power Query and create calculated columns, practicing date, conditional, and custom calculations on the orders table.

Connect to an external data source with Power Query, discover and transform the data, then close and load into an Excel pivot table for reporting.

Create a pivot table from your data source, group data in rows and columns with the field list, and summarize counts by payment type and shipper using Power Query.

Explore grouping date data in Excel pivot tables by year or month using the group option in the pivot table analyze tab, and switch between count and sum.

Use Power Query to adjust the data, add a calculated day-of-week column, load it back to Excel, and refresh the pivot table to analyze orders by day.

Explore pivot table value calculations in Excel by switching sum to count and average, using order date, order ID, payment type, and shipping fee to surface daily insights.

Convert a pivot table to a pivot chart to visualize day-of-week patterns. Use a pie chart to display the average shipping via per day, showcasing Power Query insights.

Add a slicer to your pivot table to filter data interactively, choosing days or multiple days with keyboard or the slicer controls to refine the chart.

Insert a timeline slicer from pivot table analyze to filter by date, using order date or other date fields, and switch between month, quarter, year, or day.

Save the file, open a blank workbook, connect to a Power Query data source, build a pivot table dashboard, and then close and load the data into Excel pivot tables.

Explore multiple sources in Power Query by merging an employee table with the orders table via the employee id and by appending two product lists into a master list.

Relate data in Power Query using table relationships to merge employee and orders data. Master left outer, right outer, and inner joins and when they match.

Learn to merge two data sets in Excel Power Query by linking them on a common field, creating separate queries for employees and orders, then expanding the merged results.

Append two data tables with Excel Power Query to create a master list, ensuring column headers align, enabling pivot tables and dashboards.

Connect to two Excel workbooks, examine the supplier and products data, and merge them in Power Query using the common id field to create a master list.