How to make relationship between tables by conditional?

[Anonymous]

Hello everyone,

I have two tables that I need to relate by columns (Ref, Start and End), but the beginning and end of both tables are different, I was wondering if there is any way to make the intersection between the two tables to be able to make the relationship.

Model:
. The lines (XX_00072 0 4) and (XX_00072 4 6) report to the line (XX_00072 0 6.96) because the beginning or the end intersects.

. The lines (XX_00072 6 11) and (XX_00072 11 12) report to the line (XX_00072 6.96 13.61) because the beginning or the end intersects.

Any way to do this?

Dataset Table 1:

Spoiler

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZVG7DcVACNvl6hRgcp9sEimKsv8WD5PqxQUujAXYXFc7z8fMJtrWLGtv9/ZH7lnjS44s9y+bTAKEJkDUoBohdBBkI7gyRB1UhxwdvDoOoY+ELkM6h3RRd6qH3D3KpS2xmRTNaix4c5G9Xl4dUxuTGDoqapTm4BWEh+RGKnHpV1bFapIdKaIYRBmEPhjvh1f6uH8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref", type text}, {"Start", Int64.Type}, {"End", Int64.Type}})
in
#"Changed Type"

Dataset Table 2:

Spoiler

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZNLbsQwDEPvojUXjqP4c5MBBkXvf4tSljcNvRiIeYTjEaV8v/b5/JZSejVY4a9hNvvBP74Y7LrRrreXkHXCHzEXhNUH99tbDHYXjLe1GEuHq7cgzB1T7ksIeyr87S3GMtHlXEI22lHFTAjrDj6/zISwUXGJmZCVr5f+E8Jmxy13JmSAhe+fEmtSiquiSqebUtSCp4udNMTQ7BPGXBtkERKyMmgNalOK58JQO2mIeWh5U4rWMPR/JaVg5Hp4QVZGXtRNGmIemlowlpWD1g0OyG2NyCXpTSkicnEXjMpRS0ObUlQOW76rTSlu1yklZPXTvb7v9dOubxofYD/s7KYUzdH0dFKKzuVWO2mIeTqdlGL0dfrnDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ref = _t, Start = _t, End = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref", type text}, {"Start", type number}, {"End", type number}})
in
#"Changed Type"

Note It is not possible to merge between the tables because table 1 has 300,000 rows and Table 2 has 4000 rows

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can append these two tables as new query in Power Query Editor with following codes just as suggested by @VijayP :

let
    Source = Table.Combine({#"Table 1", #"Table 2"}),
    #"Sorted Rows" = Table.Sort(Source,{{"Start", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards

[VijayP]

@Anonymous 

i think we cannot create relation ship between these tables as the ref column in both the tables is multiple times and other data is not matching.

only thing you can use Full outer and merget the tables!