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

[AnneMarie94]

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]

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]

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

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

 

[sandy666]

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]

One-to-one relationship guidance - Power BI

Guidance for understanding, developing, and working with one-to-one model relationships in Power BI.

docs.microsoft.com

Many-to-many relationship guidance - Power BI

Guidance for developing many-to-many model relationships.

docs.microsoft.com

 

[AnneMarie94]

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]

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

 

[AnneMarie94]

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]

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

 

[AnneMarie94]

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