Excel Power Query Tips and Techniques

Learn quick ways to become more organized and efficient when using Power Query, a tool inside Microsoft Excel that lets you search, connect, and shape data.

Introduction

• Get more from Power Query
• What you should know

1. Customizing Your Environment

• Disable auto detect data type
• View monospaced

2. Organizing Your Work

• Rename column
• Move, insert, and delete query steps
• Rename steps in a query
• Determining query dependencies

3. Working with Queries

• Navigate to new source
• Change load-to destination
• Reference a query
• Duplicate a query: Recycling
• Duplicate a query vs. reference a query
• Delete steps until the end
• Cross join: Matching everything with everything
• Rename queries
• Copy and paste queries to a new workbook
• Merging and segmenting data: Anti-join
• Merge queries as new

4. Power Query Peculiarities

• Filtering in Power Query
• Sorting in Power Query
• Pass parameter: Drill down to a single value
• Prevent table from resizing
• Transformation table
• Filter for certain files when importing from a folder
• Warning: Two types of merges
• Splitting columns