Transforming columns with Power Query for Excel

This will be a walkthrough of Chapter 4 of my book You do NOT need to have read the book, but you're certainly welcome to! We'll be walking through the content together for an hour and I'll try taking any questions from readers about the book.

• A recording will be made available after the event.
• All downloads used during the event will come from the book's free online repository.

• How to transform text columns to proper, uppercase, and lowercase formats in Power Query and ensure proper spacing using the Replace Values feature.
• The process of delimiting text into columns using Power Query’s Split Column by Delimiter feature and how it differs from Excel’s Text to Columns.
• How to change data types in Power Query for various columns, such as converting numbers to text or currency, to better suit the data’s purpose.
• Various ways to work with date columns in Power Query, including extracting year, month, and day into separate columns.
• How to create custom columns in Power Query using the M language, particularly for calculating ratios like winning percentages.
• Techniques for reshaping data using the Unpivot feature in Power Query to convert wide data (multiple columns) into long format (key-value pairs).

And you’ll be able to:

• Utilize Power Query to modify the case of text in columns and implement spacing corrections.
• Split text into multiple columns based on delimiters and rename these columns appropriately in Power Query.
• Change the data types of columns in Power Query to match the data’s intended use, such as text or currency.
• Delete unnecessary columns from data queries in Power Query and recover them if needed.
• Duplicate and transform date columns into separate year, month, and day columns, and understand how to format these correctly.
• Create and configure custom columns in Power Query using formulas written in the M language, and set their data type to reflect their content, such as percentage.
• Correctly apply data types in Power Query and understand the implications for Excel’s cell formatting.
• Reshape data from wide to long formats using the Unpivot function in Power Query, facilitating more flexible data analysis options.

Typical job titles might include:

• Data analysts
• Financial analysts
• Marketing coordinators
• Accountants
• Controllers
• Operations analysts

• Please have an up-to-date version of Microsoft 365 for desktop installed prior to the workshop.
• To make the most of the workshop, you will have some experience in topics such as taking sums and counts, creating calculated columns, and using relative and absolute references in Excel.
• It is recommended to attend this workshop on a Windows machine
• No other preparation is needed.

George Mount is the founder of Stringfest Analytics. He has over 10 years of experience in data analytics and holds master’s degrees in both finance and information systems from Case Western Reserve University. George is the author of (O’Reilly, 2021) and (O'Reilly, 2024). He is a recipient of the Most Valuable Professional (MVP) award from Microsoft for technical excellence & community advocacy.

I look forward to seeing you at the event!

If your team is looking for custom training in Modern Excel, check out my corporate workshops.

Event Venue

Online