Power Query Unpivot with 2 Column Variables - Bad Data Into Good Data - Excel Magic Trick 1916

Learn how to transform poorly structured cross-tabulated data with two column variables into a properly formatted dataset using Excel's Power Query in this comprehensive 10-minute tutorial. Master the process of converting bad data into good data by working with a cross-tabulated table that contains two column variables and one row variable, then restructuring it for easier analysis. Begin by converting your worksheet data into an Excel Table before importing it into the Power Query Editor, then follow a systematic approach involving multiple transpose operations, field merging, header promotion, and strategic unpivoting techniques. Discover how to handle mixed data types within a single column by separating numerical volume data from text entries indicating cyber attacks, then create separate queries for different record types. Practice essential Power Query skills including renaming steps for cleaner M Code, splitting and merging columns, applying proper data types, and creating referenced queries to maintain data integrity. Explore advanced techniques for dealing with complex data structures that require multiple transformation steps, and learn how to load your cleaned data as connection-only queries and separate worksheet tables for optimal data management and analysis workflows.

Syllabus

1. 00:00 Introduction.
2. 00:31 New Power Query book by Mike excelisfun Girvin
3. 00:52 How a cross tabulated table translates into a proper table.
4. 01:37 Convert worksheet cross tabulated table into an Excel Table BEFORE bringing into the Power Query Editor.
5. 02:55 Use keyboard shortcut to bring Excel Table into the Power Query Editor.
6. 03:20 Power Query Editor.
7. 03:46 Transpose table for first time.
8. 04:00 Rename Steps in Query to make M Code easier to read.
9. 04:47 Merge fields.
10. 05:31 Transpose table for second time.
11. 05:40 Promote Headers
12. 05:53 Name Week Column
13. 05:59 Unpivot from Week column, then rename Volume column.
14. 06:40 Split merged columns and name two new columns.
15. 07:12 Investigate Volume column to see that there are numbers that represent volumes and text entries to indicate a cyber attack in the same column.
16. 07:45 Reference base query and create new query named Cargo Records.
17. 08:10 Filter out Cyber Attack Records.
18. 08:18 Add Data Types
19. 08:33 Reference base query and create new query named Cyber Attack Records, including filtering to show only Cyber Attack Records and adding Data Types.
20. 08:55 Load base query as a connection only and the two proper table queries as new queries on new worksheets.
21. 09:37 Summary
22. 09:48 Closing