Forum Discussion
o59393
Post Prodigy
6 years agoMerged 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 ...
- 6 years agoYou 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.
danextian
Super User
6 years agoHi 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.
- o593936 years ago
Post Prodigy
Hi AllisonKennedy and AllisonKennedy
It was an error on my end that was duplicating the months.
Thanks for the help!