Merged queries is duplicating values

[o59393]

Hi all

I need to add a column from one table to another using merge queries in Power query, in order to go from this is in table called "lines" 

to this:

As seen, each of the 3 products or row, were assigned all the available months. So, column F "month" highlighted in light blue is the result desired.

When I merge the queries by common field country:

I am getting months duplicated or more by each of the 3 products, which is incorrect:

How can I get it right?

The result desired is in the excel attached.

https://1drv.ms/u/s!ApgeWwGTKtFdhn1PztJLnkIbOkgM?e=ucmaxr

Thanks!

[Syndicate_Admin]

@o59393 Good afternoon, I ask you a question. I have the same problem where "for a certain critero (reason for high / low of employees of the month) brings me the values that coincide with such a person of my payroll of employees"; thus doubling the values for certain employees. In your case how did you solve it? I'm not sure what answers you were given.

Thanks in advance

[danextian]

Hi @o59393 

@AllisonKennedy  is right. Unlike the vlookup in Excel which will return the first matching row, the Merge Query in Power BI behaves similar to SQL - you'll get all rows for every match.

[o59393]

Hi @AllisonKennedy  and @AllisonKennedy 

It was an error on my end that was duplicating the months. 

Thanks for the help! 

[AllisonKennedy · Accepted solution]

You have 'Cardboard' and 'Plastic' in your Month and Units table, so you get a month for each of those Materials, therefore you're getting 19 months * 3 rows in Lines table * 2 Materials in Month and Units table = 114 rows total.

I don't really understand why you need to duplicate the rows in the Lines table for each month? Can you use a DimDate table and some measures to achieve this result instead?

Otherwise try referencing the Month & Units table in a new query, do a Remove Duplicates on the Month column and use that for your merge.

Advanced Editor M script for the referenced query:

let
Source = Table2,
#"Removed Duplicates" = Table.Distinct(Source, {"Month"})
in
#"Removed Duplicates"

Where Table2 is your Month & units table.

Then use that new Query (name it DistinctMonths ? or something that makes sense to you) as your second table in the merge.