I need to join two data sets based on a value in one data set being between values in another data set.
Data set 1 (foo):
ID | OK |
|---|---|
to | 3 |
b | 7 |
c | 13 |
Data set 2 (bar):
description | beginning | endval |
|---|---|---|
x | 2 | 5 |
and | 8 | 14 |
z | 17 | twenty |
In SQL, you would write:
select bar.descr
, bar.beginval
, bar.endval
, foo.id
from bar
left outer join foo on foo.val between bar.beginval and bar.endval
That would produce:
description | beginning | endval | ID |
|---|---|---|---|
x | 2 | 5 | to |
and | 8 | 14 | c |
z | 17 | twenty | (null) |
But since the data sets are not coming from the same source and the sources are not necessarily SQL databases, I can't write SQL to do this in Power BI.
In Power BI, I see that I can join a column = one column. I don't see the possibility of joining a rank. Is this possible with DAX or with some kind of manipulation in Query Editor?
Update
In my specific case, data set 2 is a spreadsheet and data set 1 comes from a data center. If I join the data sets using M in Power Query Editor, as Davide Bacci recommends, it forces the result into Import mode. Power BI stopped responding and I ended up having to use Task Manager to close Power BI when I noticed there were 20 million rows in a download that probably had several hundred million rows.
Is there a way to do this in DAX so I can leave the data center part in direct query mode? Or would that still result in a slow, resource-consuming join somewhere? (Note that "slow" is relative and the main problem is the amount of computing resources required. I am currently doing this in IBM Cognos Analytics and the only problem is that the report can take up to about 18 minutes to run. But at least I don't need to upload a 400 GB data set from my laptop to the report server).
May I have asked a little question AB? I'd love to know that there are other tools in the ecosystem that can help with this. As long as it's not Power Automate. That is prohibited here.