Log In
Register
Home
Active Member
April 27, 2024
Offline
While i am doing merger from one sheet to another sheet , I can see the duplicate entry updated in worsheet.
I am attaching the cases:
Case 1: I want to find the unique id from source tab and updated in GL tab. I have a S.No which is not a duplicate number from which i did lookup and results are correct without any duplicate entry . But i have one entry which results showing as null where the serial no is blank. Now as per creteria i don't want to add serial number in GL tab as we cannot make any change in data.
Case 2 : I did the same approach and merger the query from amount instead of s.no now there are dupplicate entries appeared in GL tab.
My objective is to create a query which can extract the "Unique Code" with below creteria :
1st ) Directly lookup from S.No which is updated in Case 1 file.
2nd) Now add one more condition which will extract only for those Unique Code where the data is null and without adding any addtional entry.
I need your help on this request .
Moderators
January 31, 2022
Offline
If you intended to attach a file, please try again and don't forget to press "Start upload" before you submit your reply.
Active Member
April 27, 2024
Offline
I have attached the files now.
Moderators
January 31, 2022
Offline
Thanks for the files .I've had a look at them but don't understand what you ask. Sorry.
Can you include the expected results to both files?
Active Member
April 27, 2024
Offline
I want a unique I'd value in case 1 attachment where the value is blank . Please refer attachment case 1 tab name GL(2) cell F3 currently it's blanked however it should be RA3. That's all I need to add in power query.
Moderators
January 31, 2022
Offline
I can see the Applied Steps in the GL query. Most of them make no sense, achieve nothing and can be removed. And when you merge on s.no, it will not work as you can not tell PQ that null in one table should be matched with 3 in the other. Mergin on Name alone will work in your example but I doubt that you will have unique names in your real data.
You may merge based on Name and Amount simultaneously as these seem to be only columns that can uniquely identify the records in both tables. Even better if you could include the date, but that doesn't work in your sample as the dates for David don't match.
And that's similar to what you tried to do in Case-2 where you first merge on s.no, expand and then merge again based on Amount. However, this creates the duplicates for Andrew and David as they both have the same Amount. In real life it may be difficult to determine which of the duplicates should be removed.
See attached file in the query GL_riny that demonstrates the merge on Name and Amount.
Active Member
April 27, 2024
Offline
Hi Riny,
Thanks for reply on the request,
I have a one more question related to same amount showing in GL.
I am attaching the same workbook which you have shared with me and added few more items in Tab"SOURCE" from row 6 to 13.
After adding data from 6to 13 row and refreshed the query "GL_riny" has multiple rows created in duplicate now its 83.
Can you please review and resolve this request .
Thank you.
Moderators
January 31, 2022
Offline
Not sure I can help. What happens is logical in the context of how PQ merging works. Each row with 'David' AND '1000' will be merged with all rows in the other table containing 'David' AND '1000'. So, you end up with a lot of duplicates in a long list when you expand the column with merged tables.
The key issue is that you must find a way to uniquely identify matching records in both tables.
1 Guest(s)
