How to join two tables which are non unique

[Jakez]

Hi All,

I have two tables as shown below and i want to merge them together to do some analysis in Powerbi, original source of the data is excel files. I want to join the table using two columns Dealer_id and Date.  

Table 1

Dealer_id	Date	L_id	Count	Alt_c
2344	01/01/2023	23	2345	333
2344	02/01/2023	32	4234	224
2344	03/01/2023	22	4245	3456
2344	04/01/2023	44	4242	243

Table 2

Dealer_id	Date	Sales
2344	01/01/2023	4500
2344	02/01/2023	3499
2344	03/01/2023	2133
2344	04/01/2023	2344

Desired Output

Dealer_id	Date	L_id	Count	Alt_c	Sales
2344	01/01/2023	23	2345	333	4500
2344	02/01/2023	32	4234	224	3499
2344	03/01/2023	22	4245	3456	2133
2344	04/01/2023	44	4242	243	2344

What i  really need from  is to get the Sales from table 2 into table 1,  i get the desired output in powerquery but when i apply and close Power query and when the data loads i get the following error "Column Dealer_id in table1 contains a duplicate value '2344' and this is not allowed for columns on the one side of a many to one relationship or for columns that are useed as primary key of a table"

PLease advise how i can solve this.

Thanks

[AntrikshSharma · Accepted solution]

@Jakez 

1. Disable "Auto Detect Relationship" under file settings.
2. Before loading data make sure there are no existing relationships.
3. You can join 2 table by using DealerID and Date as the Key

let
    Source = 
        Table.NestedJoin (
            Table1,
            { "Dealer_id", "Date" },
            Table2,
            { "Dealer_id", "Date" },
            "Table2",
            JoinKind.LeftOuter
        ),
    ExtractedSales = Table.ExpandTableColumn ( Source, "Table2", { " Sales" }, { " Sales" } )
in
    ExtractedSales

[Dikshant]

Works fine, Thanks Antriksh!

For the uninititated, you have to create a new Query to paste the above code in Advanced editor.

Also, remove the space before {" Sales"} to avoid error.

[ronrsnfld]

I cannot reproduce your problem with the information you have provided. How, exactly, did you merge the tables in Power Query?

I merely selected to get data from Excel; selected the file and then the two tables.

In PQ, I selected to merge the two tables to a new query, using a full outer join, and then extracting the Sales column.

If you did something different, please tell us what you did, and supply the M-Code from the Advanced Editor.