Implementing error checks in Power Query
Sam Fischer Sam Fischer

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.

Read More
Dealing with Gaps and Islands in Power Query
Sam Fischer Sam Fischer

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.

Read More
Convert a date range to a list of dates with Power Query
Sam Fischer Sam Fischer

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.

Read More
Rounding datetimes to nearest minute or hour in Power Query
Sam Fischer Sam Fischer

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.

Read More