Advanced Conditional Splitting - Power Query Challenge 42

Advanced Conditional Splitting - Power Query Challenge 42

Do you need to split text data into dynamic parts without relying on helper columns? In this video, I’ll show you how to conditionally split text values in Power Query based on their length using a clean and efficient method. Whether the text has an even or odd number of characters, we’ll break it down into 2 or 3 parts—no fluff, just smart transformations.

This tutorial is perfect for anyone looking to level up their Power Query skills and handle dynamic text transformations with precision.

In This Video:
📌 Overview of the Challenge
Understand the task: split IDs based on their character count.
📌 Step 1: Transform the Column to a List
Learn how to convert a table column into a list and why this step is essential for flexible transformations.
📌 Step 2: Apply List.Transform
Use the powerful List.Transform function to iterate over each value and build custom logic.
📌 Step 3: Calculate the Length and Midpoint
Create dynamic variables to measure the text length (Text.Length) and calculate the midpoint (Number.RoundDown) for even and odd-length values.
📌 Step 4: Write Conditional Logic for Splitting
Build the logic to split text into two or three parts using Text.Start, Text.Middle, and Text.End.
📌 Step 5: Extract Results
Learn how to extract the desired lists of results from the records created in earlier steps.
📌 Step 6: Convert Lists to a Table
Combine everything into a clean, structured table using the Table.FromRows function and add column names.

What You’ll Learn:
✔️ How to use List.Transform for dynamic transformations without helper columns.
✔️ How to apply conditional logic to split text based on even or odd character counts.
✔️ How to leverage Text.Start, Text.Middle, and Text.End for precise text extraction.
✔️ A practical approach to transforming lists into structured tables with minimal steps.

This technique is both powerful and elegant, perfect for those who want to maximize Power Query’s potential.
Resources Mentioned:

🔗 Power Query Challenges Playlist:
   • Power Query: Challenges  

✅ Like and subscribe for more Power Query challenges!
✅ Have any questions or suggestions? Let me know in the comments!

⏰ Timestamps:
0:00 Introduction
0:44 Step 1: Reference Column and Transform to List
1:11 Step 2: Apply List.Transform Function
1:55 Step 3: Turn Values into Records
2:14 Step 4: Add Length and Midpoint to Record
2:40 Midpoint Calculation Logic
2:55 Step 5: Conditional Splitting Logic
3:36 Splitting with Text.Start, Text.Middle, and Text.End
3:48 Step 6: Extract Result Field
4:01 Step 7: Turn Lists into a Table
4:29 Result: Final Split Table
4:37 Closing Thoughts and Engagement
4:43 Outro and Call to Action


🔔 Subscribe for More Tutorials:
   / @howtolearnexcel  

Links:
CHALLENGE AUTHOR: Omid Motamedisedeh
1) LinkedIn Profile:   / omid-motamedisedeh-74aba166  
2) Power Query Challenge: https://www.linkedin.com/posts/omid-m...
3) Download the file: https://docs.google.com/spreadsheets/...