How to compare two big excel files by power query? Sheet1.xls and Sheet2.xls Both haveThousand rows and more than 50 columns each . they have complete different columns unless 2 coluns C end D are common in both. .I want to have sheet 3 with all lines and coluns of sheet1 except all lines( when the line C and D lines) matchs sheet 2 ? Pls. Basically i want extract the difference Thanks
Can it be done for multiple columns, I'd be working with sales files where one column will always have duplicates such as invoice numbers. I wouldn't be removing those. I would like to remove a row of 20 columns where the entire row is an obvious duplicate that must be removed.
Hi Dough! If I wanted to flag the duplicates in Power Query. Say a calculated column, the first line would have a 1 and all duplicates below it should show a 0. Is this possible in PQ?
Hi DougThanks for this video. How can I not only identify duplicate but remove them using power query. I was trying to use COUNTIF function to label first occurrence as 0 and other will follow as 1,2,3...Is there a power query formula to the duplicate in number of occurrence the way countif fn will count them in excel sheet. Thanks
I'm Assuming you are using the conditional formatting feature (ua-cam.com/video/meogoA_MXmg/v-deo.html). You could use the errors to define the conditional format if you meant #N/A. That is an error condition that you can define a conditional format. If it is just N/A, use the condition criteria in quotes (i.e., text equals "N/A")
Doug, Hi. Im not sure if i explained my self correctly. Here is what im trying to do, is the following: When cell A-1 shows the word "split-shift", i would like the cell below that, that is A-2, and A-3 to change its cell color to the word "open". Which part of the conditional format option/function would you recommend me to try, to make this work. Thank you. Thank you for the fast respond earlier. Dan
Why not just go to "Keep Rows" > "Keep Duplicates" - to identify them... Easy peasy. Then you can always X out the step it creates after identifying them.
Thanks, Doug that's what I was looking for!
Glad I could help!
Thats exactly what i needed today! Thanks
You’re welcome!!
@@DougHExcel Thanks again! 🙌🔥🙏
This was amazing Doug!! Subscribed immediately! Problem solved!
Thanks for the sub!
Thanks buddy! Your video still rocks!!
Thanks! You rock!
Nice video sir. Great job explaining and giving the extra tips along the way !
Glad you liked it
Thank you so much for your tutorial!
You’re welcome!
Great tip, Doug.
Hi Randy Smith, thanks for the comment!
This was really nice. Can I split this workbook into 2 worksheets with PowerQuery? One with Duplicates and the other No Duplicates? Thanks
Yes you can. Just reference the first one you created and make that the duplicate or non duplicate query (diff name) and load to sheet.
@@DougHExcel Thank You!
How to compare two big excel files by power query? Sheet1.xls and Sheet2.xls Both haveThousand rows and more than 50 columns each . they have complete different columns unless 2 coluns C end D are common in both. .I want to have sheet 3 with all lines and coluns of sheet1 except all lines( when the line C and D lines) matchs sheet 2 ? Pls.
Basically i want extract the difference
Thanks
Maybe this one will give some insight ua-cam.com/video/GlMFBNQUQQ8/v-deo.html
Thank you sir
Welcome!
How to do look up in power query for large data sir.. Data exceeded excel maximum limit
Try to use CSV file as your source. However the output tab will have the constraint.
Thanks!
You’re welcome!
Can it be done for multiple columns, I'd be working with sales files where one column will always have duplicates such as invoice numbers. I wouldn't be removing those. I would like to remove a row of 20 columns where the entire row is an obvious duplicate that must be removed.
Yep, it can
Hi Dough! If I wanted to flag the duplicates in Power Query. Say a calculated column, the first line would have a 1 and all duplicates below it should show a 0. Is this possible in PQ?
This vid might give some insights ua-cam.com/video/P1OzKWWWpVY/v-deo.html
Hi DougThanks for this video. How can I not only identify duplicate but remove them using power query. I was trying to use COUNTIF function to label first occurrence as 0 and other will follow as 1,2,3...Is there a power query formula to the duplicate in number of occurrence the way countif fn will count them in excel sheet. Thanks
Thank you Doug, pls can you paste this link again because there were no video in the above link you pasted.
Try this..
ua-cam.com/video/P1OzKWWWpVY/v-deo.html
Thanks
Thanks
You’re welcome!
hi Doug,
if I want cell a2 change it's cell color, every time cell a1, shows the word N/A. what formula would I need to use ? thank you. dan
I'm Assuming you are using the conditional formatting feature (ua-cam.com/video/meogoA_MXmg/v-deo.html). You could use the errors to define the conditional format if you meant #N/A. That is an error condition that you can define a conditional format.
If it is just N/A, use the condition criteria in quotes (i.e., text equals "N/A")
Doug,
Hi. Im not sure if i explained my self correctly. Here is what im trying to do, is the following:
When cell A-1 shows the word "split-shift", i would like the cell below that, that is A-2, and A-3 to change its cell color to the word "open". Which part of the conditional format option/function would you recommend me to try, to make this work. Thank you.
Thank you for the fast respond earlier. Dan
how to find partial duplicate in power query
maybe one of these related can give insight ua-cam.com/video/fzfN84kDqgU/v-deo.html
@@DougHExcelthanks..bt this is not what I want..
Why not just go to "Keep Rows" > "Keep Duplicates" - to identify them... Easy peasy. Then you can always X out the step it creates after identifying them.
Thank you❤
You're welcome 😊