Implementing error checks in Power Query
Allowing incorrect data to appear in a report can diminish trust in your organisation’s reporting capability. To avoid incorrect data appearing in the first place, we can force refresh errors when certain business rules aren’t met (since usually stale data is better than incorrect data). In this post, I demonstrate a pattern for applying custom error messages when business rules are violated. These messages can be set up to provide error details that cut down troubleshooting time.
Dealing with Gaps and Islands in Power Query
Gaps and Islands analysis is a useful tool for finding distinct periods in a list of dates, like if you wanted to know when each customer was active or inactive. In this post, I show you a technique for finding gaps and islands in a list of dates against multiple categories, and provide custom M functions that you can use yourself for these kinds of problems.
Convert a date range to a list of dates with Power Query
In this post, I’ll show you how you can take a range of dates (defined by a start date and end date), and convert it into a full list of dates. This technique is by no means new, but we’ll expand this approach by converting the logic into a custom function in Power Query so you can repeat the logic in separate queries.
Rounding datetimes to nearest minute or hour in Power Query
One of the most common culprits behind a large data model in Power BI is the presence of columns with a high cardinality. A common scenario where this occurs is when you’re dealing with event data, where you have precise datetimes down to the second for a series of events. For many applications, knowing the precise second that an event occurs isn’t as important as knowing what minute or hour it occurred in. In this post, I’ll introduce a Power Query function you can use to round a datetime field down to the nearest minute, 15 minutes or hour. This can reduce cardinality and lead to a more compact report file size.