Working with ranges (Numbers/Dates) in Power Query/Power BI - Part 2

[rsaprano]

One of the challenges I had working with a client recently was around how to work with ranges/bands of numbers in Power BI so thought I’d share the approach I took to this. I was looking to deal with this in the data preparation layer – i.e. using Edit Queries – as this is the most appropriate place to get my data in shape before it is loaded into the data model.

In Part 1 of this blog series, I covered Doing an ‘inexact’ lookup, a VLOOKUP in Excel with a TRUE parameter at the end. This looks up a number against a set of bands/ranges and returns the appropriate band which the number sits in. 

In this part, we look at finding overlapping ranges: This is the scenario where we have two ranges/bands of numbers (rather than having one number and one set of bands) and we want to see the areas of overlap between the ranges. An example of this might be where we have multiple ranges of dates, and we want to see which/how many days are common between the two sets.

The example dataset I will be using for this is shown

Set 1 Range Name	Set1RangeFrom	Set1RangeTo
Set 1 Range 1	50	100
Set 1 Range 2	35	150
Set 1 Range 3	60	70
Set 1 Range 4	5	7

Set 2 Range Name	Set2RangeFrom	Set2RangeTo
Set 2 Range 1	0	9.9
Set 2 Range 2	10	19.9
Set 2 Range 3	20	29.9
Set 2 Range 10	90	99.9
Set 2 Range 15	140	149.9
Set 2 Range 16	150	159.9

And the desired result is to see all combinations where there is some level of overlap (e.g. the start of the range in set 1 is within a range within set 2, or the whole range sits within a range within set 2). In this case the output required is as per the following table:

Set 1 Range Name	Set1RangeFrom	Set1RangeTo	Set 2 Range Name	Set2RangeFrom	Set2RangeTo	Overlap
Set 1 Range 1	50	100	Set 2 Range 4	60	80	Set 2 Range within Set 1 Range
Set 1 Range 1	50	100	Set 2 Range 5	90	99.9	Set 2 Range within Set 1 Range
Set 1 Range 2	35	150	Set 2 Range 4	60	80	Set 2 Range within Set 1 Range
Set 1 Range 2	35	150	Set 2 Range 5	90	99.9	Set 2 Range within Set 1 Range
Set 1 Range 2	35	150	Set 2 Range 6	140	149.9	Set 2 Range within Set 1 Range
Set 1 Range 3	60	70	Set 2 Range 4	60	80	Set 1 Range within Set 2 Range
Set 1 Range 4	5	7	Set 2 Range 1	0	9.9	Set 1 Range within Set 2 Range

 Overlapping Ranges         

The first step I take in considering any challenge which isn’t completely straightforward is to go back to traditional means – I grab a pen and paper and draw out what I need. In this case I considered the various scenarios under which there could be overlap between ranges of numbers and drew them up as follows:

From this analysis, we can see that there are only 4 scenarios of overlap between two ranges of numbers.

Having identified the four key scenarios under which there is potential overlap, putting them into Power Query code became much simpler.

I picked one set where I would invoke the function from (i.e. use the from/to range from column values in the table) – I picked set 2 for this – and the other set (set 1) I’d then pass in the from/to values as input parameters.

The code I then needed to write for this was simply to define tables (variables in Power Query) using Table.SelectRows for each overlap scenario. The Table.SelectRows function then returns all the rows in the table that meet the conditions specified.

To make it easier to understand, I also added a column with a description of each overlap scenario using Table.AddColumn, calling the column "Overlap" in each case with a value of a textual description of the overlap scenario.

Once I have all my overlapping ranges as separate variables, I simply returned a variable which is the union of all the individual overlap scenarios above.

The code for this then looks like:

(Set1RangeFrom as number, Set1RangeTo as number) =>

let

    A_Buffertable = Table.Buffer(tblSet2),

    B_Scenario1 = Table.SelectRows(A_Buffertable, each [Set2RangeFrom] <= Set1RangeFrom and [Set2RangeTo] >= Set1RangeTo),

    C_Scenario1WithCol = Table.AddColumn(B_Scenario1, "Overlap", each "Set 1 Range within Set 2 Range"),

    D_Scenario2 = Table.SelectRows(A_Buffertable, each ([Set2RangeFrom] >= Set1RangeFrom and [Set2RangeTo] <= Set1RangeTo)),

    E_Scenario2WithCol = Table.AddColumn(D_Scenario2, "Overlap", each "Set 2 Range within Set 1 Range"),

    F_Scenario3 = Table.SelectRows(A_Buffertable, each  [Set2RangeFrom] < Set1RangeFrom and [Set2RangeTo] > Set1RangeFrom and [Set2RangeTo] < Set1RangeTo),

    G_Scenario3WithCol = Table.AddColumn(F_Scenario3, "Overlap", each "Start of Set 1 Range within Set 2 Range"),

    H_Scenario4 = Table.SelectRows(A_Buffertable, each Set1RangeFrom < [Set2RangeFrom] and [Set2RangeTo] > Set1RangeTo,

    I_Scenario4WithCol = Table.AddColumn(H_Scenario4, "Overlap", each "End of Set 2 Range within Set 1 Range"),

    J_Union = Table.Combine({C_Scenario1WithCol,E_Scenario2WithCol,G_Scenario3WithCol,I_Scenario4WithCol})

in

    J_Union

Once I created this function I was ready to invoke it on each row of the Set 1 table (passing in the Set1RangeFrom and Set1RangeTo from the current row as arguments) and the result was all the overlaps between Set 1 and Set 2.

That's it! 

I've attached a PBIX showing both examples. I hope you find this technique useful for other similar scenarios and please let me know either through the comments or a message whether you have any questions on implementing this for your particular case.