Forum Discussion
Comparing Multiple Columns with a single measure or calculated column?
Hi all - I'm comparing two categories for differences but each category has multiple columns (i.e. category 1 (current columns) has 82 columns vs category 2 (first columns) has 82 columns as well). I'd like to know the difference per each columns recording only the data under category 2 if there's a difference.
All of the data has a first and current version column in the dataset. I am trying to compare multiple columns to see if the same value is there in the "current" version column or if another value is in one of the "first" columns. For example in the table below, I'm looking to determine which of the values have been added/removed or stayed the same between the current and first version columns.
| Current Column 1 (1) | Current Column 1 (2) | Current Column 1 (3) | First Column 1 (1) | First Column 1 (2) | First Column 1 (3) | Target Metric |
| 2 | 4 | 2 | 2 | 34 | 1 | Added: 34 and 1 Removed: 4 and 2 |
| 3 | 5 | 4 | 3 | 6 | 2 | Added: 6 and 2 Removed: 5 and 4 |
| 5 | 6 | 8 | 5 | 67 | 34 | Added: 67 and 34 Removed: 6 and 8 |
| 4 | 45 | 6 | 1 | 8 | 6 | Added: 1 and 8 Removed: 4 and 45 |
Any ideas on how best to approach this? Thanks!!
1 Reply
- lbendlin
Super User
You will want to transform the data into a usable format using unpivot.
Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.
