Comparing Data and Values Between Two Tables

[a68tbird]

Hello All -

  I have two tables, Sales and SalesUpload.  Both tables have the same columns, and all data types match.  I need to find the variances/differences between the two tables, and identify which rows are producing those variances. I created a table using the EXCEPT function,:

Variance = EXCEPT(Sales,SalesUpload)

but I received this error: "Function 'EXCEPT' does not support joining a column of type Date with a column of type Text." I couldn't quite understand this since all of the data types are correct and appropriate. Turns out (and I believe this is where the problem is originating) that if I view the two tables in the "Data" view, the column order is different - even though, if I edit the queries, the columns in both tables are in the same order.  Somehow, when I load the query, the column order gets changed. Don't know how to fix that, since you can't move columns around in the "Data" view. 

So, if I it's possible to correct that column ordering, would the EXCEPT function return rows where the sales total value is different? If not, how could I go about comparing the values between the two tables? The data gets kind of granular - the columns are:

Thanks for any help. 

[parry2k]

@a68tbird sounds good. Let me know if you need further assistance. at least you are one step closer 🙂

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@a68tbird can you make sure RecognizationDate doesn't have any space in the front of one of the tables. in the Power bi data model, columns are sorted alphabetically, so in the Sales table case RecognizationDate is at the end and that tells me that it has probably space or other character making it not to sort alphabetically, and that's probably is the issue.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[a68tbird]

Thanks @parry2k .  There were no spaces or strange characters in that column name. I copied the column name from one table to the other, so that I knew they would be the same, and that didn't help. Even renamed the columns, and didn't change anything.  So I removed that column (I could probably get by without needing it), and finally got the EXCEPT table to work.  But it seems to be returning way more than I was expecting.  I can see that there is a bit of a difference in the totals between the two tables, but the Variance table is returning 100 times as much.  Will have to examine the data or find another solution to this.

[parry2k]

@a68tbird source type for the 2nd screen shot is any not text, and also when you say orders get changed, where you see these change of orders, share the screen shot?

[a68tbird]

Good catch.  🙃

Here's how the tables look when viewing in the "Data" tab:

[parry2k]

@a68tbird yes, they both need same number of columns, in same order and same data type

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[a68tbird]

Thanks @parry2k - as mentioned, I've made sure that both tables have the same columns, and data types all match.  Here are screenshots of both:

However, the column order changes when I "Close & Apply", and as such creates that error on the EXCEPT function.