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.
10 ways you can use Calculation Groups in Power BI
When I first started learning about Calculation Groups, I could certainly see how much potential this new DAX feature would eventually have. The global Power BI community has certainly proven me right, as they’ve discovered plenty of great uses for them! Here are my top 10 use cases for them, spanning time intelligence, dynamic formatting, controlling relationships and enhancing the user experience…
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.