Forum Discussion
Merged queries is duplicating values
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!
- 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.
4 Replies
- 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. 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.Hi AllisonKennedy and AllisonKennedy
It was an error on my end that was duplicating the months.
Thanks for the help!
@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
