Nice work. Thanks for sharing this. I will definitely see if this can work for us. I am not sure if rows can always be in exactly the same order for most of the comparisons I see, but maybe there is some way to impose some kind of sort and then do a similar analysis.
Hello. If you filter and keep the nulls, you would be excluding the records that were not null, but had different amounts in both columns. However, you could use that method to count records that were in one table and not another.
Great video without using merge and joints. Would you make a video to reconcile 2 tables, both with 3 rows - date, particulars and amounts. The recon should be done first with particulars matching, and then if particulars dont match, with dates matching
Yes, I will be posting one on how to reconcile amounts with multiple fields involved. It will be the same basic process, however you create a unique key by concatenating the fields together before the pivot. i.e. Date&"-"&Number&"-"&Department, etc. Then parsing this back out once the zeros are filtered out, to see differences by field.
That's a great way to do the same thing! I recorded the "pivot table method" since I thought it would be easier to follow and learn for those already familiar with Excel Tables, and or Pivot Tables.
Hi iankr. I don't think you have to, but working within the data model allows us to build the solution within that virtual space. Then, return just what we need to complete the report within Excel.
Super useful tutorial and well demonstrated. Thanks 🙏
It is so useful and easy to understand. Thank you much.
This is easy
In practice its more complicated where files are from different systems havono references
How to do that where there is no common key?
Nice work. Thanks for sharing this. I will definitely see if this can work for us. I am not sure if rows can always be in exactly the same order for most of the comparisons I see, but maybe there is some way to impose some kind of sort and then do a similar analysis.
If my data for table 1 & 2 are large, what is the most efficient way to replace the data?
At 8:10, instead of subtraction, could you just filter and keep the nulls?
Hello.
If you filter and keep the nulls, you would be excluding the records that were not null, but had different amounts in both columns. However, you could use that method to count records that were in one table and not another.
Great video without using merge and joints. Would you make a video to reconcile 2 tables, both with 3 rows - date, particulars and amounts. The recon should be done first with particulars matching, and then if particulars dont match, with dates matching
Yes, I will be posting one on how to reconcile amounts with multiple fields involved. It will be the same basic process, however you create a unique key by concatenating the fields together before the pivot. i.e. Date&"-"&Number&"-"&Department, etc. Then parsing this back out once the zeros are filtered out, to see differences by field.
@@AZAdvancedAnalytics thank you, will wait for it. Please inform when video is available
Why not use merge queries and do joins
That's a great way to do the same thing! I recorded the "pivot table method" since I thought it would be easier to follow and learn for those already familiar with Excel Tables, and or Pivot Tables.
Indeed. The equivalent of Venn diagrams is PQ are those join types.
Hi
Many thanks for that. But did you really need to add the queries to the Data Model?
Hi iankr. I don't think you have to, but working within the data model allows us to build the solution within that virtual space. Then, return just what we need to complete the report within Excel.
@@AZAdvancedAnalytics I was wondering the same thing, if adding to the Data Model was really needed, but thanks for a very interesting approach.