Forum Discussion

Money's avatar
Money
Icon for Helper I rankHelper I
3 years ago

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

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. 

 

 

6 Replies

    • Money's avatar
      Money
      Icon for Helper I rankHelper I
      3 years ago

      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. 

  • andhiii079845's avatar
    andhiii079845
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      Money
      Icon for Helper I rankHelper I
      3 years ago

      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.