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

[Anonymous]

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.

[v-juanli-msft · Accepted solution]

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.

[Greg_Deckler]

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.