Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to make relationship between tables by conditional?

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

2 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

    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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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