Creating a new table with a calculated column by joining two tables depending on the match

[Money]

Hi All,

I am trying to create a grid by joining two tables from two different data sources. One of IBM DB2 another of databricks. Unique ID Both the tables are Line Segment and Track Number.  From there, we compute the Exceptions column according to the common condition below.

Based on this condition have to match the two tables

1. Requires same LS & Track Number

Mp range = Thor mp - cwr mp

2. Defines a Tight MP range -0.01 to 0.03 was recommended

3. Defines a Loose MP range 0.1 to 0.2 was recommended

4. Preferred to match with same rail ( thor rail = cwr rail ) within the loose MP range, but would match with opposite rail if there was no other joint around

* Below mentioned 4 points are joining condition for the 2 tables.

* MP range is THOR MP - CWR MP = that difference should satisfy the Tight Mp range and Loose Mp range.

* 4th point describes Thor rail and cwr rail from the table.

Two Tables Matching Condition 

Thor Data screenshot 

CWR Data Screenshot

Expected output screenshot 

In the ablove shared expected output screenshot is the required output to get in the power bi report and Thor Id should match with the single CWR ID and its should not duplicate. exception column is derived from the definition shared by first image.

In the below link attached the sample Data of two tables (THOR table and CWR table ) in the excel sheet with required expected output. Needs to get the power Bi report similar to the output.xls file from joining the two table.

https://ln5.sync.com/dl/bd0dd0280/h2wb67uu-a676nb9f-hmakf6m9-3ymfkzeb 

@tamerj1  @amitchandak @Greg_Deckler @Arul @v-zhangti @PowerQuerryGuru @Ashish_Mathur 

Please do the needful, I am new to power bi. 

[andhiii079845]

The join via Line Segment and Track Number is not possible. You have duplicates in both tables. You need a unique value in both table. How should Power BI decide what belongs together? 

[Money]

@andhiii079845 In both the table that 2 columns are the only match column then thor table is the left table and we are using left outer join later we are doing some business logic calculation to avoid the duplicates but we are not getting expected result.  Sharing screenshot for your reference from actual data. red box are duplicates value. one Thor ID should match with one CWR ID.

[andhiii079845]

How should one Thor ID match one CWR ID? 

[Money]

Based on this condition

1. Requires same LS & Track Number

Mp range = Thor mp - cwr mp

2. Defines a Tight MP range -0.01 to 0.03 was recommended

3. Defines a Loose MP range 0.1 to 0.2 was recommended

4. Preferred to match with same rail ( thor rail = cwr rail ) within the loose MP range, but would match with opposite rail if there was no other joint around

[andhiii079845]

Do you try to merge the tables in Power Query Editor? Is this possible? 

https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616...

[Money]

@andhiii079845  We have implemented the merging option and next we are not able to achive that output result which is shared in the question above.