Help transforming a SQL join to a PowerQuery query.

[Anonymous]

I have been trying to transform this query:

SELECT
ms.groupid,
ms.statusid,
ms.Description,
ms.begintime,
ms.endtime,
i.uid
FROM DWH.AAAA AS ms
LEFT JOIN DWH.BBBB AS i ON
i.[begin] >= ms.begintime
AND i.[begin] < ms.endtime
AND i.re = ms.re
AND i.groupid = XY
AND i.visualisation = 'Z'

To the right equivelant in Powery Query. So far, the design page (DataFLow) has only allowed me to create this:

let
  Bron = Table.NestedJoin(
    #"MES - AAAA", {"begintime"}, 
    #"MES - BBBB", {"begin"}, "MES - BBBB", 
    JoinKind.LeftOuter)
in
  Bron

[Anonymous]

TableA

Id	Description	Start	End
1	qpwoei	13.00	14.00
2	alskdj	13.00	14.00

TableB

Id1	Id2	Id3	Start	End	Groupid	Visualisation
8	2	3	12.30	13.30	XY	Z
9	4	5	12.30	13.30	XY	Z

Joined

KeyA	KeyB (concatenated)
1	8.2.3
9	9.4.5

[lbendlin · Accepted solution]

see attached for an implementation loosely based on your sample data.

[Anonymous]

Dear sir, thanks for the assistence, i have been able to get this to work now in pbi. Im currently working on translating it to dataflow. Thanks again.

[lbendlin]

That looks ok-ish.  Where are you stuck? You need the other where conditions too?

Instead of Table.NestedJoin you can use Table.AddColumn with a custom column generator function that can implements your fuzzy join for you,

Note that your "left"  join is in fact an inner join since you have conditions from the "right"  table as part of the join.

[Anonymous]

Im stuck in the fact that i cannot use > but only join on a specific column (equals).

[lbendlin]

As I said, use Table.AddColumn with a custom column generator.

Like so

NextStep = Table.AddColumn(LeftTable,"Join",(k)=> Table.SelectRows(RightTable,each
[begin] >= k[begintime]
and [begin] < k[endtime]
and re = k[re]
and groupid = XY
and visualisation = "Z"
))

This will be a guaranteed left join even with the conditions on the right.

[Anonymous]

I see what you are doing, cant get it to work though. Must add that im trying to set this up in DataFlow. Im wondering, even if this works, what does it look like when it finds miplt records, and therefore, would it be possible to create a calculated table for this such as:

TableA

ID	Description	Starttime	Endtime
1	pqowie	13.00	14.00
2	laksjd	13.00	14.00

TableB

id1	id2	id3	Start	End	GroupId	Visualisation
8	1	2	12.50	13.30	XY	Z
9	1	3	12.50	13.30	XY	Z

Joined

TableAId	TableBId (concatenaded)
1	823
1	913
2	823
2	913

1 matches with 823 because it starts before 1300 and end before 1400. The same if true for 2. Basically we are matching events from TableA, with TableB, requiring events from TableB to have started before the TableA event and ended before the TableA event ended. 

So creating a table for setting up a one to many relationship

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

[Anonymous]

Tried to sanitize.

[lbendlin]

Paste sample TableA and sample TableB and indicate the expected result.

[Anonymous]

Hope this works better.