Excel Power Query Course: Power Query Tutorial for Beginners

Simon Sez IT・2 minutes read

Alan Murray offers a comprehensive Excel course focusing on Power Query, Power Pivot, and Dax for data analysis and transformation, with practice exercises provided in each module. The course covers essential features like filtering, splitting columns, renaming headers, loading data, merging queries, appending data, and creating conditional columns for efficient data manipulation and analysis in Excel.

Insights

Power Query, Power Pivot, and Dax are essential tools in Excel for importing, transforming, and analyzing data, offering rich features for data preparation and complex calculations.
Detailed steps on data manipulation, such as splitting columns, capitalizing words, and renaming headers, provide a structured approach to enhance data quality and formatting within Excel.
Functions like merging queries, appending data, and unpivoting columns in Power Query offer advanced data transformation capabilities, enabling efficient data analysis and consolidation for comprehensive insights.

Get key ideas from YouTube videos. It’s free

Recent questions

What are the main tools in Excel for data analysis?
Power Query, Power Pivot, and Dax.

Related videos

Summary

00:00

"Excel Power Tools for Data Analysis"

Alan Murray is an Excel instructor and consultant with over 20 years of experience.
The course focuses on Power Query, Power Pivot, and Dax in Excel.
Power Query helps import, transform, and load data for analysis.
Power Pivot is Excel's data model for large data volumes.
Dax is the formula language of Power Pivot for data preparation and calculations.
Practice exercises and quizzes are included in each course module.
Power Query simplifies data import from various sources and offers rich transformation tools.
Power Pivot allows creating pivot tables from multiple tables in the data model.
Dax is a powerful formula language for complex calculations in Power Pivot.
The Power Query editor window includes essential features like ribbon tabs, applied steps, and column profiling.

21:47

"Transform and Load Data in Power Query"

In Power Query, filter out null values by unchecking them after clicking on the filter icon in the column headers, similar to Excel.
Split column 1 containing dates and store names by selecting the column, clicking on the split column button on the Home tab, and choosing a delimiter, such as space, to separate the date and store.
Choose the leftmost delimiter option when splitting the column to ensure correct separation, especially for store names like "New York" with spaces.
Capitalize each word in the second column to improve data format by clicking on the transform tab, then the formats button.
Rename headers for all three columns by double-clicking on them and entering "date," "store," and "amount" respectively.
Check and adjust data types for each column, changing the third column to a currency data type by clicking on the data type button.
Load the data into a table in the worksheet by clicking on the close and load button on the Home tab, choosing the existing worksheet option, and specifying the range.
Edit an existing query by removing unnecessary steps, such as change type steps, by right-clicking on the step and choosing delete.
Rename steps in the applied steps pane for clarity and better organization by right-clicking on the step and selecting rename.
Refresh the query to update data by selecting the refresh all option from the data tab, ensuring new data is loaded and transformed correctly for analysis.

41:20

Effective File Management in Excel: Tips & Tricks

To filter out specific file types, uncheck them in the extension column filter arrow.
Consider future scenarios when excluding file types to avoid issues.
Use text filters like "begins with" and enter the desired extension to filter files effectively.
Combine contents of multiple files into one table using the combined files button in the content column header.
Ensure column headers have consistent names for proper stacking in Excel.
Remove unnecessary extensions like ".CSV" from the source name column using the replace values function.
Capitalize each word in the source name column using the transform function.
Rename columns and check data types before loading the data.
Load data by selecting "create connection" and adding it to the data model.
Save the file under a specific name for future use and refresh the query to include new data.

59:54

"Power Query: Edit Paths, Merge, Append"

To ensure proper operation, the file path provided in the source must be edited to match the path you will be working from.
If the formula bar is not visible, access it by clicking on the View tab and selecting the formula bar checkbox on the left-hand side.
In the applied steps, you can edit the path by clicking on the gear icon next to the source step or directly in the formula bar.
Similar adjustments need to be made for the three queries originating from the workbook, such as D categories, D products, and D reps, by browsing to the folder and selecting it.
The unpivot columns feature in Power Query allows for transforming data from a pivot table format to a more analyzable structure.
Unpivoting columns can be done by selecting specific columns or excluding columns to ensure flexibility for future data changes.
The merge queries function in Power Query is utilized to combine data from different tables based on a common column, similar to a VLOOKUP function in Excel.
The merge query process involves selecting the tables, specifying the relationship column, choosing the join kind (e.g., left outer join), and bringing in the desired columns.
The merge query can be used not only to merge data but also to compare tables and remove any matching items, ensuring data accuracy and consistency.
The append query function in Power Query allows for stacking queries together, such as adding new products from a text file to an existing products table, expanding the dataset efficiently.

01:19:44

"Appending New Products, Expanding Data Set"

In My Documents folder, new products are found for import, adding three new products to the existing seventeen.
Power Query opens on the right, showing the new products named "new - products."
The data is checked and looks good, then appended to the main table "D products" using the append queries option.
The append window appears, allowing the selection of the new products table for appending.
The data is successfully appended, adding the samosa, blueberry muffin, and caramel shortbread, totaling 20 products.
The category ID column is added due to the new products list, causing null values for existing products.
To fix this, the order of applied steps is changed by moving the append query before the merge queries.
The data now shows the new products without the merges taking place, totaling 22 products.
The first merge query is run, expanded, and a column removed, resulting in the final data set.
The queries are then tidied up by renaming them for clarity and loaded back into the worksheet for further analysis.

01:39:14

Excel: Table Transfer, Query Creation, Region Assignment

To move tables between worksheets in Excel, select the desired table, cut it, and paste it into the target sheet to consolidate reports.
Unwanted sheets can be deleted or hidden, with deleted sheets automatically converting the associated query to a connection-only status.
Power Query allows for the creation of multiple outputs from a base query, enabling the generation of reports, pivot tables, or other data representations.
Creating conditional columns in Power Query involves setting conditions based on specific values, such as store IDs, to assign regions to each store.
The process includes removing unnecessary rows, editing headers, and utilizing the conditional column feature to assign regions to stores based on predefined criteria.

Try it yourself — It’s free.