Troublesome Data Patterns

Reshaping and preparing data for analysis is often a combination of art and science, blending theory and skills with creative problem-solving. There are a wide range of troublesome data patterns, from dealing with stacked data to multiline headers and beyond. Challenges are not limited to the structure of the data alone but often also include inconsistencies and other complexities that can arise from the unique nature of the data itself or specific business rules and requirements.

When we think about problem-solving, it’s important to remember that there are many different approaches and techniques. In this book, we’re not claiming to have all the answers or that these methods are best. Rather, we aim to offer a collection of ideas and spark your imagination to help you kick-start your problem-solving journey, making you confident in facing any troublesome data pattern that crosses your path.

The examples provided throughout this chapter...

Pattern matching

Pattern matching is an essential skill often associated with regular expressions (or regex), which is important for identifying and manipulating specific patterns in textual data. Although the Power Query M language lacks direct support for regex, it provides a wealth of functions that can help address these types of requirements. In this section, we explore pattern matching within the constraints of Power Query, highlighting some key techniques and strategies for addressing these challenges.

Basics of pattern matching

Pattern matching is incredibly useful for finding and extracting specific character sequences from data. That process relies heavily on the use of text functions. The M language is equipped with various functions that operate on text values. These functions can facilitate certain aspects relevant to pattern matching. They offer a range of capabilities, from basic to more advanced string operations.

Here, we’ll delve into various considerations...

Combining data

Although the process of extracting and combining data may seem straightforward at a glance, it can quickly turn into a complex and demanding task. The level of complexity largely depends on the characteristics of the data and its organization. From the perspective of this section, it’s important to understand that by combining data we mean an append operation, typically seen when consolidating files.

In the real world, data often originates from various sources and is managed by multiple users, which complicates maintaining consistency across all files. Excel workbooks, in particular, is a very common format, as Excel is used extensively across many industries for numerous data-related tasks.

In this section, we’ll delve into techniques and strategies to overcome challenges commonly faced when working with multiple Excel files. While some are specific to the nature of Excel, others are more generic and applicable across different scenarios.

...

Summary

In this chapter, we have highlighted practical aspects of data manipulation and preparation by focusing on two very different topics: extracting fixed patterns and combining data from multiple Excel files. Although they feel worlds apart, these skills, among many others, are both essential for transforming raw data into insightful, actionable information.

By breaking down processes into manageable pieces, underscoring the importance of a methodical approach as well as creative problem-solving, we aimed to offer a collection of ideas, fuelling your imagination to tackle any challenge. The next chapter focuses on another critical area: optimizing performance. You will learn about factors that influence, and methods to improve, query performance.

Learn more on Discord

Join our community’s Discord space for discussions with the author and other readers:

https://discord.gg/vCSG5GBbyS