topic Re: Duplicate Rows after Power BI Merge in Power Query

Duplicate Rows after Power BI Merge

Anonymous — Thu, 28 Apr 2022 13:07:55 GMT

I have two spreadsheets of rented Units that have different accounting data I want to consolidate. The spreadsheets are brought into different Power BI tables and are merged using a column I create from joining the same data in both tables. Everything is fine in each table until I expand the newly merged colum. At this point I have duplicate rows in different portions of the merged table. I have tried highlighting the 7 affected columns and Remove Duplicates but nothing is removed. Please advise as to what I am doing wrong.

Thank you

Re: Duplicate Rows after Power BI Merge

Anonymous — Thu, 28 Apr 2022 15:49:17 GMT

Thank you for looking, I fouind my mistake and rectified it with a simple remove duplicates for one row.

I guess I just needed to write it down and turn it over in my mind.

Re: Duplicate Rows after Power BI Merge

Anonymous — Thu, 28 Apr 2022 18:57:15 GMT

It seems the duplicate removal works well except for three rows wher it duplicates itself. I have been unable to figure out a work around so far. I took a couple of screen shots and have uploaded them.

I have uploaded the Merge Query.

This is the result after dupes are removed Column 362 - 367

Re: Duplicate Rows after Power BI Merge

AlexisOlson — Thu, 28 Apr 2022 19:26:38 GMT

I don't quite understand what you are trying to do. Are you intentionally using a fuzzy match?

What do your original tables look like and what should the final result look like?

Re: Duplicate Rows after Power BI Merge

Anonymous — Mon, 02 May 2022 12:41:08 GMT

Thank you for the response. I am using the fuzzy match as it is the only one I have found that matches the columns correctly other than column 362-364 those columns should have a null returned not the TRI 2-207. I am merging two tables where I have created a Unit Building column common to both tables. The tables have differing info that I would like to have in one place.

Thank you again for your assistance.

Re: Duplicate Rows after Power BI Merge

Anonymous — Mon, 02 May 2022 14:16:07 GMT

Alex,

Thank you for the response. I am using the fuzzy query as otherwise the query is not returning the total rows. The difficulty is Row 362--364 shou;ld return a null value via the merge but are returning a value of Tri 2-207 that is the correct value returned in row 365. The balance of the Merge seems to be correct; it is just the three rows that should be returning 203-205 and are instead returning Tri 2-207.

Re: Duplicate Rows after Power BI Merge

Anonymous — Thu, 05 May 2022 06:13:40 GMT

Hi Anonymous ,

It seems that you have found a solution to your problem, right?

It would be great if you could mark your reply as a solution.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Duplicate Rows after Power BI Merge

ilaydacandan — Fri, 03 Feb 2023 21:53:48 GMT

Hi All,

I am not sure if the issue was solved or not but I had a case like below and solved it with append queries. Here is the situation:

Table 1

B/L Number	Revenue
1	100
2	200

Table 2

B/L Number	Cost Amount
1	-50
1	-50
2	-50
3	-200

When I selected merge it was dupplicating the the line in revenue table because there were 2 of the some b/l number. So when I selected append here is the result:

B/L Number	Revenue	Cost
1	100	-50
1	null	-50
2	200	-50
3	null	-200