# Power Query, Merge, One-to-One, full outer join. Results are one-to-many join



## AnneMarie94 (Oct 13, 2020)

Thanks for your help. In a single workbook, I have two tables (on separate worksheets) each as the same 14 columns. table 1 has 1276 rows, table 2 has 989 rows.  I have connections to each table in the workbook queries list. I merged the queries with table 1 first then table 2. I selected the year columns first and then the country columns next.  The merge editor said that 1274 rows matched for both columns. I asked for a full outer join and clicked Okay. Then in the Query Editor I expanded the rows for table 2. I selected all but one column which I knew had no data in it. 

I could see in the Query Editor that this had not performed a one-to-one join. Instead it did a one-to-many join. Not only is this not what I wanted. But, it increased the size if the table exponentially. How can I get a one-to-one join. I'm pretty sure that I used this exact method two weeks ago to successfully join two other tables.


----------



## sandy666 (Oct 13, 2020)

__





						Merge queries (Power Query) - Microsoft Support
					

A merge query creates a new query from two existing queries. One query result contains all columns from a primary table, with one column serving as a single column containing a navigation link to a related table.




					support.microsoft.com


----------



## AnneMarie94 (Oct 13, 2020)

sandy666 said:


> __
> 
> 
> 
> ...



Thank you for your reply. However, it does not answer my question.


----------



## sandy666 (Oct 14, 2020)

AnneMarie94 said:


> I could see in the Query Editor that this had not performed a one-to-one join. Instead it did a one-to-many join. Not only is this not what I wanted. But, it increased the size if the table exponentially. How can I get a one-to-one join. I'm pretty sure that I used this exact method two weeks ago to successfully join two other tables.


add tables to Data Model then join them as you wish (if it will be possible), I really don't know what you did two weeks ago


----------



## sandy666 (Oct 14, 2020)

One-to-one relationship guidance - Power BI
					

Guidance for developing one-to-one model relationships.



					docs.microsoft.com
				











						Many-to-many relationship guidance - Power BI
					

Guidance for developing many-to-many model relationships.



					docs.microsoft.com


----------



## AnneMarie94 (Oct 14, 2020)

Thank you for your references. I will follow up with them. I'm not familiar with Power BI right now but will try working with it.  Is there no way to do a one-to-one join or merge in Power Query or regular Excel? I have a lot more experience with those. I think that this dataset would be too large for VLOOKUP but I'm not sure.


----------



## sandy666 (Oct 14, 2020)

it works with Power Query and Power Pivot (Data Model) in Excel 2016, you don't need to use PBI


----------



## AnneMarie94 (Oct 14, 2020)

Two weeks ago, all three tables had the same number of rows that matched each other for both the year and country columns.

Thanks for the tip. I have put my tables into a data model. Unfortunately, Power Pivot won't let me create relationships between the year or country columns because each cell doesn't have unique data in it. The rows are unique because of the combination of the year and country data put together. Would you suggest that I create a new column in each table with a combo of the year and country as an identifier for each row?  Then bring the tables into the data model?  Is there a better way to do this?  

Thank you for your help.


----------



## sandy666 (Oct 14, 2020)

you can do that in Data Model, just new column with concatenated data from Year and Country columns, the same for both tables


----------



## AnneMarie94 (Oct 15, 2020)

I was not able to get that to work. But, figured out another way to do it.   Thanks for your help.


----------



## AnneMarie94 (Oct 13, 2020)

Thanks for your help. In a single workbook, I have two tables (on separate worksheets) each as the same 14 columns. table 1 has 1276 rows, table 2 has 989 rows.  I have connections to each table in the workbook queries list. I merged the queries with table 1 first then table 2. I selected the year columns first and then the country columns next.  The merge editor said that 1274 rows matched for both columns. I asked for a full outer join and clicked Okay. Then in the Query Editor I expanded the rows for table 2. I selected all but one column which I knew had no data in it. 

I could see in the Query Editor that this had not performed a one-to-one join. Instead it did a one-to-many join. Not only is this not what I wanted. But, it increased the size if the table exponentially. How can I get a one-to-one join. I'm pretty sure that I used this exact method two weeks ago to successfully join two other tables.


----------



## sandy666 (Oct 15, 2020)

Glad you solve it
Have a nice day


----------

