Preface / How to Learn Power Query: A Weapon for Success / This Book's Structure / Download Example Files

CHAPTER 01 Getting Started with Power Query

SECTION 01 How to Use Power Query by Excel Version
Users of Excel 2010 and 2013 versions
Excel 2016 users
Excel 2019 or later, Microsoft 365 version users
SECTION 02 Understanding the Structure of Tables to Help Utilize Power Query
Table
Cross-Tab
Template
SECTION 03 Data Sources Accessible in Power Query
Explore the [Import Data] submenu
SECTION 04 Creating Queries Using Power Query
[Practice] Creating queries by accessing data from other files
[Practice] Generating table data from the current file using a query
[Practice] Understanding How Queries Process and Refresh Data

CHAPTER 02 The 8 Most Used Features in Power Query

SECTION 01 Function ①: Filling
[Practice] Using [Fill] when using a table containing merged or empty cells
SECTION 02 Function ②: Changing values
[Practice] Using [Change Value] when [Fill] doesn't work
SECTION 03 Function ③: Data Format Conversion
[Practice] Converting Date Data in Power Query
[Practice] Transforming Numeric Data in Power Query
SECTION 04 Function ④: Filter
[Practice] Understand how to apply filters to retrieve only the data you want through a query.
[Practice] Understanding How to Change Filter Conditions in an Existing Query
SECTION 05 Function ⑤: Data Sorting
[Practice] Understanding how to sort source data the way you want
SECTION 06 Function ⑥: Heat removal and heat selection
[Practice] Understanding how to select and import only the necessary columns from another table.
SECTION 07 Function ⑦: Column Splitting
[Practice] Recognizing delimiters and separating columns
[Practice] Understanding how to separate numbers from text
SECTION 08 Function ⑧: Removing duplicate items
[Practice] Querying to remove duplicate data from a specific column in the original table and return the results.
[Practice] Understanding How to Leave the Last Data When Deleting Duplicate Data

CHAPTER 03 Four Features to Make Power Query More Powerful

SECTION 01 Function ⑨: Release the column pivot
[Practice] Converting Tables to Easily Summarize Data
[Hands-on] Unpivoting a Table's Columns Using Merge
SECTION 02 Function ⑩: Grouping
[Practice] Summarizing a Table Using Grouping
[Practice] Displaying required items by connecting them with commas using grouping.
SECTION 03 Function ⑪: Pivot Column
[Practice] Understanding How to Summarize Data Using Pivot Columns
[Practice] Learn how to convert date data in Power Query.
SECTION 04 Feature ⑫: Custom Column
[Practice] Understanding how to create new columns by adding calculated columns.
[Practice] Understanding how to create new columns using example columns
[Practice] Understanding how to create desired columns using conditional columns
[Practice] Understanding How to Use Index Columns

CHAPTER 04: 3 Key Features for Advanced Power Query Utilization

SECTION 01 Function ⑬: Merge
[Join Type] 6 Options
Left outer
[Practice] Understanding how to concatenate two queries using [Left Outer]
[Practice] Understanding how to merge two tables when there are more than two matching columns.
[Practice] Understanding how to merge two tables when one table has multiple columns to be linked.
[Practice] Understanding how to convert a table to combine repeated columns into a single column when the same column is repeated 1
[Practice] Understanding how to convert tables to merge repeated columns into a single column when the same column is repeated 2
Right outer
[Practice] Understanding how to merge two table queries with [Right Outer]
Full outer
[Practice] Understanding How to Merge Queries from Two Tables [Completely Outer]
Inner
[Practice] Create a list of members that exist in both tables, then randomly select three.
Left anti and right anti
[Practice] Understand how to compare two queries and extract data that exists only in one query.
SECTION 02 FUNCTION ⑭: ADDITIONAL
[Practice] Understanding how to add multiple queries into one query
[Practice] Understanding how to add data from multiple sheets into a single query
SECTION 03 Function ⑮: In the folder
[Practice] Consolidating a table of files within a specific folder into a single table and then summarizing it using a pivot table.
[In the workbook] Function

CHAPTER 05 Various Power Query Utilization Tips

SECTION 01 How to read data from photos
[In the photo] Features
SECTION 02 How to read PDF data
[In PDF] Features
SECTION 03 Web Data Crawling Methods
stock market data
Holiday data
SECTION 04 How to copy only the query to another file
Copy-paste
Copy M code
Create and share an Office connection file

Search

The Weapon of a Hard Worker 01.
Learn the 8 most commonly used Power Query basic functions in practice!

Among the various functions of Power Query, we will introduce eight functions that are relatively easy to use and frequently used in practice: [Fill], [Change Values], [Convert Data Type], [Filter], [Sort Data], [Remove Columns and Select Columns], [Split Columns], and [Remove Duplicate Items].
If you master these functions, you can obtain the data you want much more easily and conveniently than by using Excel functions, formulas, macros, etc.

The Weapon of a Hard Worker 02.
Learn four practical Power Query features that will boost your work productivity!

Now that you've learned the eight features above and are somewhat familiar with how to use Power Query, let's look at four practical features that will help you use Power Query more effectively: [Unpivot Columns], [Grouping], [Pivot Columns], and [Custom Columns].
Mastering these features will help you understand Power Query more deeply and use it more effectively.

The Weapon of a Hard Worker 03.
Learn three key features to further leverage Power Query!

Power Query offers a variety of ways to manage your data more efficiently, not just by creating a single query, but by integrating multiple queries.
Mastering the three features of Power Query—Merge, Append, and From Folder—will greatly simplify complex data operations and automate repetitive tasks.

+Learn with free YouTube video lectures!

Video lectures are provided on the author's YouTube channel (5 lectures in total).
When learning through video, you can upgrade your practical skills by learning from the author's YouTube lectures that are effective.

What kind of readers is this book for?

- Those who want to easily handle large amounts of practical data using Power Query
- Those who want to learn the powerful Power Query function that goes beyond the limitations of Excel functions and macros.
- Anyone who wants to know how to manage, process, and analyze data with Excel
- Those who want to develop data literacy skills in the big data era