Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Merge two table columns with many to many relationship creating variables / summarize function

I have two tables where the only similar column is "Company_Supplier_Id" and I need to merge the following columns into one table:

 

Table 1 Name: looker_views lkr_Company_Supplier_Site_Via

1. Company_Supplier_Name

2.Company_Supplier_Id

 

Table 2 Name: looker_views lkr_Intake_Data

1.Company_Supplier_Id

2.Intake_Data_Id

3.Intake_Data_Custom_1

 

I have tried creating my own table with defined variables however it keeps giving me an error:

 

No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.

 

MyTable =
VAR SupplierSiteVia =
SELECTCOLUMNS(
'looker_views lkr_Company_Supplier_Site_Via',
"Supplier Name merge",'looker_views lkr_Company_Supplier_Site_Via'[Company_Supplier_Name],
"Supplier Id merge", 'looker_views lkr_Company_Supplier_Site_Via'[Company_Supplier_Id]
)
VAR IntakeDataMerge =
SELECTCOLUMNS(
'looker_views lkr_Intake_Data',
"Intake Data Supplier Id merge",'looker_views lkr_Intake_Data'[Company_Supplier_Id] ,
"Intake Data Id merge",'looker_views lkr_Intake_Data'[Intake_Data_Id] ,
"Custom 1 merge", 'looker_views lkr_Intake_Data'[Intake_Data_Custom_1]
)
VAR Result = NATURALLEFTOUTERJOIN(SupplierSiteVia,IntakeDataMerge)
RETURN Result
 
 
There is a many to many relationship between the tables would this present the problem?

 

or

 

Should i be building this using a summarize new column ?

 

 

Any help would be greatly appreciated.
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    You use "selectcolumns" to create two new tables and join them, but there is no common column in both table.

    To use the "join/NATURALLEFTOUTERJOIN", please rename the columns names in your "selectcolumns" statement.

    For example, 

     

    MyTable =
    VAR SupplierSiteVia =
    SELECTCOLUMNS(
    'looker_views lkr_Company_Supplier_Site_Via',
    "Supplier Name merge",'looker_views lkr_Company_Supplier_Site_Via'[Company_Supplier_Name],
    "Supplier Id merge", 'looker_views lkr_Company_Supplier_Site_Via'[Company_Supplier_Id],
    "id",[id]
    )
    VAR IntakeDataMerge =
    SELECTCOLUMNS(
    'looker_views lkr_Intake_Data',
    "Intake Data Supplier Id merge",'looker_views lkr_Intake_Data'[Company_Supplier_Id] ,
    "Intake Data Id merge",'looker_views lkr_Intake_Data'[Intake_Data_Id] ,
    "Custom 1 merge", 'looker_views lkr_Intake_Data'[Intake_Data_Custom_1],
    "id",[key]
    )
    VAR Result = NATURALLEFTOUTERJOIN(SupplierSiteVia,IntakeDataMerge)
    RETURN Result

     

    Best Regards

    Maggie

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Very likely that it is your many-to-many that is causing the issue. If you read the documentation on the DAX join functions, they call out one-to-many relationships at the end of the documentation.
  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    You use "selectcolumns" to create two new tables and join them, but there is no common column in both table.

    To use the "join/NATURALLEFTOUTERJOIN", please rename the columns names in your "selectcolumns" statement.

    For example, 

     

    MyTable =
    VAR SupplierSiteVia =
    SELECTCOLUMNS(
    'looker_views lkr_Company_Supplier_Site_Via',
    "Supplier Name merge",'looker_views lkr_Company_Supplier_Site_Via'[Company_Supplier_Name],
    "Supplier Id merge", 'looker_views lkr_Company_Supplier_Site_Via'[Company_Supplier_Id],
    "id",[id]
    )
    VAR IntakeDataMerge =
    SELECTCOLUMNS(
    'looker_views lkr_Intake_Data',
    "Intake Data Supplier Id merge",'looker_views lkr_Intake_Data'[Company_Supplier_Id] ,
    "Intake Data Id merge",'looker_views lkr_Intake_Data'[Intake_Data_Id] ,
    "Custom 1 merge", 'looker_views lkr_Intake_Data'[Intake_Data_Custom_1],
    "id",[key]
    )
    VAR Result = NATURALLEFTOUTERJOIN(SupplierSiteVia,IntakeDataMerge)
    RETURN Result

     

    Best Regards

    Maggie

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.