This tip is GREAT! Thanks so much. Just a disclaimer for anyone trying to do this with Power Query in Excel -- doing this is a bit clunky in Excel. I find that this doesn't work when loading Table2 from inside of Table1's Excel file (or vice versa). I would open Table1's excel file and in Power Query within that same file I would load Table2 -- but this did not work...some rows appeared to be removed but quite a large amount of matching rows still were kept. I tried this numerous times and ensured the tables were structured the same but nothing seemed to work. It wasn't until I started a brand new workbook (Book1) and loaded BOTH Table1 and Table2 inside of Book1..then it worked....it seems that the source path needs to be the same for both Table1 and Table2. Regardless, this is a great tip, thanks again.
Hi, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
@@EnterpriseDNA Been an early follower of Sam’s Develpment of EDNA right from the start. Great to see the team he has attracted. Watch just about every video.
Hi Melissa. Thanks for this tutorial. I have always done this through the UI via Table Merge with Table1 first and Table2 second and join type as Right-Anti (rows only in second). Now I have another way to accomplish this task. Thanks for sharing :)) Thumbs up!! PS - Could also be done as Left-Anti (rows only in first) if Table2 is first and Table1 is second.
Hi, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
Hi Ricardo, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
Thanks for the vid. Great content. I have a question with a wrench. If I want to compare 2 tables that i'm getting from a data warehouse, and they have 100 columns. Is there another way for me to do that without having to type in the 100 column names in the parameter?
Hi Raimundo, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Hello Prapulla, In this video, we're already making comparison's between the records from two tables and evaluating the results accordingly, at a record level. We're not able to understand what you meant by "Does this logic work for record-to-record comparison?" In case, you want an alternative solution to this then try using "Merge Queries" option and evaluate the results. For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our RUclips channel and LinkedIn group to keep posted on Power BI, Power Platform, and the Microsoft stack updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/
Thanks for sharing. Is there an opposite to the INNER Join eg., get the information which is UNCommon between 2 tables. The longer method would be to make the same query like you explained above twice for both the tables and then append them; is there any better alternative?
Hello, Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post. Well, in that case, you can select the option of "Full Outer" where rows which remains unmatched from both the tables are taken into consideration. For furthermore queries, you can also reach out to us onto our Enterprise DNA Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Hoping you find this useful. Also, make sure that you're subscribed to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ruclips.net/user/EnterpriseDNA www.linkedin.com/groups/12004506/
Hi Melissa, This worked like a charm. I have a question and hope you can help. How do I identify what is different in each row? Out of 39 Column headers, only 5 Columns match from DW to ERP Migration, and the Query returned over 1000 rows with differences and do not want to manually pick through them one-by-one🙂
Hi Kim, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful. Also, make sure that you're subscribed to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/
Hi PK, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. We'll take note of your suggestion. Thanks!
Hi Adriel, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Hello, I'm still new to PowerBI and I got a question, How to create a new column for a result to comparing DateTime from, let's say Tab 1 and the Shift time from Tab 2, and this new column will be created in the Tab 1?
Hello Jonathan Gabe, Thank you for posting your query onto our channel and we really appreciate you taking your time to post it. But it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. But what we think is, you're trying to do some sort of Lookup between the tables and extract the value from one column of a table to put it in another. So based on that we're providing few of the links below from our blog posts which might help you in your query. Hoping you find this useful and helpful. Also, make sure that you're subscribed to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ blog.enterprisedna.co/multiple-currency-logic-in-power-bi-lookupvalue-example/ blog.enterprisedna.co/update-to-multiple-currencies-management-in-power-bi-advanced-dax/ blog.enterprisedna.co/how-to-deal-with-products-that-have-changing-prices-overtime-in-power-bi/
Hi! Glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
Hi Rokutime, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Hi, I'm new with Power BI, I need to create a table from other two identical tables and showing only the rows that was renewed(with a different date, NOT THE NEW ONES). Basically the third one table have to include only the rows when a field is different compared the table 1 but not the new rows. Eg. Table 1 and table 2 are identical but have the second last column(date - "valid to") and the last column(date - "report date") different. I want to find the rows that differs in the "valid to" column, and skip the "report date" because is of course always different and skip the new rows. Eg. TABLE1 CI_NUM CI_NAME VALID_TO DATE_REPORT 0000 TEST1 01.02.2021 23.10.2021 0001 TEST2 03.04.2020 23.10.2021 0002 TEST3 19.07.2021 23.10.2021 TABLE2 CI_NUM CI_NAME VALID_TO DATE_REPORT 0000 TEST1 01.02.2099 09.12.2021 0001 TEST2 03.04.2020 09.12.2021 0002 TEST3 19.07.2021 09.12.2021 0004 TEST4 23.09.2025 09.12.2021 RESULT: CI_NUM CI_NAME VALID_TO DATE_REPORT 0000 TEST1 01.02.2099 09.12.2021 Giving the CI_NUM, CI_NAME and VALID_TO as final arguments it doesn't work because gives me also the new ones(0004, TEST4...) Thank you.
Hello Salvatore, Thank you for posting your query onto our channel. We really appreciate your query and the valuable time that you've taken to post it. Well it's always a little bit difficult to judge and provide the results pertaining without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. But based on the scenario we might have little bit of an idea about what you're trying to achieve here. We're providing few of the links links of the videos from our Enterprise DNA RUclips channel pertaining to this topic which might be helpful in your scenario. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful. Also, make sure that you're subscribed to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ ruclips.net/video/sWEtboeHJl8/видео.html ruclips.net/video/jpo_4q4YyfQ/видео.html ruclips.net/video/opYn_wDngMI/видео.html forum.enterprisedna.co/t/field-by-field-comparison-of-two-tables-auto-compare-two-queries/19583 forum.enterprisedna.co/t/comparing-two-table-and-appending-mismatches/19348
This tip is GREAT! Thanks so much. Just a disclaimer for anyone trying to do this with Power Query in Excel -- doing this is a bit clunky in Excel. I find that this doesn't work when loading Table2 from inside of Table1's Excel file (or vice versa). I would open Table1's excel file and in Power Query within that same file I would load Table2 -- but this did not work...some rows appeared to be removed but quite a large amount of matching rows still were kept. I tried this numerous times and ensured the tables were structured the same but nothing seemed to work. It wasn't until I started a brand new workbook (Book1) and loaded BOTH Table1 and Table2 inside of Book1..then it worked....it seems that the source path needs to be the same for both Table1 and Table2. Regardless, this is a great tip, thanks again.
Hi, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
Melissa - Was just working on exact problem this would be a solution for. Thanks again Enterprise DNA for videoing more and more of the lesser known.
Hi Wayne, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
@@EnterpriseDNA Been an early follower of Sam’s Develpment of EDNA right from the start. Great to see the team he has attracted. Watch just about every video.
Hi Melissa. Thanks for this tutorial. I have always done this through the UI via Table Merge with Table1 first and Table2 second and join type as Right-Anti (rows only in second). Now I have another way to accomplish this task. Thanks for sharing :)) Thumbs up!! PS - Could also be done as Left-Anti (rows only in first) if Table2 is first and Table1 is second.
Hi Wayne, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
@@EnterpriseDNA Awesome! Thanks.. I have subscribed :))
Was looking for this since three days for exactly this problem. Today my luck clicked.
Hi P Tyagi, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Excelent Melissa, power query has a lot of options!
Hi Benhur, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Excellent solution for comparing two tables!
Hi, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
Another golden nugget! Thanks Melissa
Hi Roberto, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Awesome video. Very easy to understand and straight to the pont. Thanks!
Hi Ricardo, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
Thans from Brazil, Melissa! ❤
What a great function. Thanks for sharing.
Hi Vida, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Thanks for the vid. Great content. I have a question with a wrench. If I want to compare 2 tables that i'm getting from a data warehouse, and they have 100 columns. Is there another way for me to do that without having to type in the 100 column names in the parameter?
Perfect! Thank you so much! You really helped me a lot.
Hi Raimundo, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
How do we workaround, when tables are interchanged, if Table 1 is a table returns a empty table and Table.ToReocrd is Table2.
Excellent! Thank you! 👏
Hi Mariusz, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Hi, Does this logic work for record-to-record comparison?
Hello Prapulla,
In this video, we're already making comparison's between the records from two tables and evaluating the results accordingly, at a record level. We're not able to understand what you meant by "Does this logic work for record-to-record comparison?" In case, you want an alternative solution to this then try using "Merge Queries" option and evaluate the results.
For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out!
If you haven't yet, do subscribe to our RUclips channel and LinkedIn group to keep posted on Power BI, Power Platform, and the Microsoft stack updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
Hola Melissa, es una belleza, bendiciones!
Thanks for sharing. Is there an opposite to the INNER Join eg., get the information which is UNCommon between 2 tables.
The longer method would be to make the same query like you explained above twice for both the tables and then append them; is there any better alternative?
Hello,
Thank You for posting your query onto our channel. We really appreciate you taking your valuable time to post.
Well, in that case, you can select the option of "Full Outer" where rows which remains unmatched from both the tables are taken into consideration.
For furthermore queries, you can also reach out to us onto our Enterprise DNA Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner.
Hoping you find this useful. Also, make sure that you're subscribed to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ruclips.net/user/EnterpriseDNA
www.linkedin.com/groups/12004506/
Dat is erg handig! thanks Melissa!
Hi Melissa, This worked like a charm. I have a question and hope you can help. How do I identify what is different in each row? Out of 39 Column headers, only 5 Columns match from DW to ERP Migration, and the Query returned over 1000 rows with differences and do not want to manually pick through them one-by-one🙂
Hi Kim,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
Hoping you find this useful. Also, make sure that you're subscribed to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
www.linkedin.com/groups/12004506/
Thanks Melissa, amazing stuff. It would be nice if you could zoom in on the videos like in the other EDNA content
Hi PK, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. We'll take note of your suggestion. Thanks!
Awesome! Thanks a lot!
Hi Adriel, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Hello, I'm still new to PowerBI and I got a question, How to create a new column for a result to comparing DateTime from, let's say Tab 1 and the Shift time from Tab 2, and this new column will be created in the Tab 1?
Hello Jonathan Gabe,
Thank you for posting your query onto our channel and we really appreciate you taking your time to post it. But it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
But what we think is, you're trying to do some sort of Lookup between the tables and extract the value from one column of a table to put it in another. So based on that we're providing few of the links below from our blog posts which might help you in your query.
Hoping you find this useful and helpful. Also, make sure that you're subscribed to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
www.linkedin.com/groups/12004506/
blog.enterprisedna.co/multiple-currency-logic-in-power-bi-lookupvalue-example/
blog.enterprisedna.co/update-to-multiple-currencies-management-in-power-bi-advanced-dax/
blog.enterprisedna.co/how-to-deal-with-products-that-have-changing-prices-overtime-in-power-bi/
Thanks so much !!!
thanks dear it was helpfull
Hi! Glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!
434 likes to 5 dislikes. Fire the RUclips CEO
Hi Rokutime, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Hi, I'm new with Power BI, I need to create a table from other two identical tables and showing only the rows that was renewed(with a different date, NOT THE NEW ONES).
Basically the third one table have to include only the rows when a field is different compared the table 1 but not the new rows.
Eg. Table 1 and table 2 are identical but have the second last column(date - "valid to") and the last column(date - "report date") different.
I want to find the rows that differs in the "valid to" column, and skip the "report date" because is of course always different and skip the new rows.
Eg.
TABLE1
CI_NUM CI_NAME VALID_TO DATE_REPORT
0000 TEST1 01.02.2021 23.10.2021
0001 TEST2 03.04.2020 23.10.2021
0002 TEST3 19.07.2021 23.10.2021
TABLE2
CI_NUM CI_NAME VALID_TO DATE_REPORT
0000 TEST1 01.02.2099 09.12.2021
0001 TEST2 03.04.2020 09.12.2021
0002 TEST3 19.07.2021 09.12.2021
0004 TEST4 23.09.2025 09.12.2021
RESULT:
CI_NUM CI_NAME VALID_TO DATE_REPORT
0000 TEST1 01.02.2099 09.12.2021
Giving the CI_NUM, CI_NAME and VALID_TO as final arguments it doesn't work because gives me also the new ones(0004, TEST4...)
Thank you.
Hello Salvatore,
Thank you for posting your query onto our channel. We really appreciate your query and the valuable time that you've taken to post it.
Well it's always a little bit difficult to judge and provide the results pertaining without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.
But based on the scenario we might have little bit of an idea about what you're trying to achieve here. We're providing few of the links links of the videos from our Enterprise DNA RUclips channel pertaining to this topic which might be helpful in your scenario.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful. Also, make sure that you're subscribed to our RUclips channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ruclips.net/channel/UCy2rBgj4M1tzK-urTZ28zcA
www.linkedin.com/groups/12004506/
ruclips.net/video/sWEtboeHJl8/видео.html
ruclips.net/video/jpo_4q4YyfQ/видео.html
ruclips.net/video/opYn_wDngMI/видео.html
forum.enterprisedna.co/t/field-by-field-comparison-of-two-tables-auto-compare-two-queries/19583
forum.enterprisedna.co/t/comparing-two-table-and-appending-mismatches/19348
How do we workaround, when tables are interchanged, if Table 1 is a table returns a empty table and Table.ToReocrd is Table2.