Forum Discussion
Power Query merging query creating duplicate rows
adrianmihaita Your query merges the two other based on License ID alone. Since you have 13 periods (Year-Month) for each license, PQ will create 13 X 13 = 169 rows for each one. You need to base the merge (LeftOuter) on Licence ID, Year and Month.
First select License ID in both left and right, then press and hold Ctrl and select Year in both and then Month in both. LeftOuter and OK. Expand the relevant columns from the merged tables and you get the billing report, one row for each license, year and month. Thus, 13 rows for each license.
I will try my best to explain my situation in a few words and I will attach an archive with some files (I have anonymized the real data for security reasons).
I need to prepare a report that will allow me to see the billing for a contact center with multiple clients.
I`m struggling to get the report finished because I get duplicate rows after merging two appended queries.
I have tried in two ways:
- Get data from a folder (12 xlsx files with two sheets, one sheet with the number of agents and cost of the licensing and another sheet with the cost of the outgoing/incoming calls charges). I have created two queries for each of the sheets and after that I have merged this two n a separate query. Everything looks fine up until I get to the merged query were all the data gets duplicated multiple times.
- Get data from each xlsx files and create 2 append query for each of the sheets in every xlsx file and then merge the two appended queries. Here I could see the same behavior.
Thank you!
adrianmihaita Your query merges the two other based on License ID alone. Since you have 13 periods (Year-Month) for each license, PQ will create 13 X 13 = 169 rows for each one. You need to base the merge (LeftOuter) on Licence ID, Year and Month.
First select License ID in both left and right, then press and hold Ctrl and select Year in both and then Month in both. LeftOuter and OK. Expand the relevant columns from the merged tables and you get the billing report, one row for each license, year and month. Thus, 13 rows for each license.
Riny_van_Eekelen thank you for point out the obvious fact ... now it makes sense! 🙂
Thank you very much for your prompt and fast reply