Forum Discussion
Evaluate text field value and create a column based on unique occurrences
Hello everyone,
I have a huge data center usage dataset that contains a colum "EquipmentId". The Equipment ID colulmn contains alpha numerical values. In a million plus rows, there are a couple of hundred unique equipment Ids. I need create a column that associates each unique equipment Id to IT department.
I have it working by using IF statements but it's not a very elegant solution, IMO. Looking for your help on how this can be done perhaps by inserting another table etc.?
Here is the sample dataset:
| EquipmentId | Cost | Datacenter | Date |
| 123456abc | 100 | South | 1/3/2024 |
| 98765xyz | 104 | North | 1/4/2024 |
| 87234234kj | 201 | South | 2/1/2024 |
| 9876234bs | 300 | South | 2/5/2024 |
| 27342asdf | 100 | North | 2/8/2024 |
| 123456abc | 219.8 | South | 1/3/2024 |
| 98765xyz | 239.4 | South | 1/4/2024 |
| 87234234kj | 259 | North | 2/1/2024 |
| 9876234bs | 278.6 | South | 2/5/2024 |
| 27342asdf | 298.2 | South | 2/8/2024 |
| 123456abc | 317.8 | North | 1/3/2024 |
| 98765xyz | 337.4 | South | 1/4/2024 |
| 87234234kj | 357 | South | 2/1/2024 |
| 9876234bs | 376.6 | North | 2/5/2024 |
| 27342asdf | 396.2 | South | 2/8/2024 |
| 123456abc | 415.8 | South | 1/3/2024 |
| 98765xyz | 435.4 | North | 1/4/2024 |
| 87234234kj | 455 | South | 2/1/2024 |
| 9876234bs | 474.6 | South | 2/5/2024 |
| 27342asdf | 494.2 | North | 2/8/2024 |
I have a separate table with all unique equipment Ids and it's correspondending department here:
| EquipmentId | Department |
| 123456abc | Hosting |
| 98765xyz | Communication |
| 87234234kj | HelpDesk |
| 9876234bs | Hosting |
| 27342asdf | HelpDesk |
I need to create a slicer and filter for reporting on cost per department. Your help is greatly appreciated.
Go to 'Transform Data'
Select the table with all of your data and then select 'Merge Queries'
Select the mapping table and then select the 'EquipmentId' column for both tables...
After clicking 'OK' you will have a new column added to your table that has a table in each row.Expand that column. Select only the Department and deselect the 'Use original column name...'
After clicking OK you should have the desired result in your table...
4 Replies
jgeddesSuper User
Have you considered merging the two tables together using the 'EquipmentId' column as the link and then expanding the resulting column to include only the 'Department"?
- jgeddes
Go to 'Transform Data'
Select the table with all of your data and then select 'Merge Queries'
Select the mapping table and then select the 'EquipmentId' column for both tables...
After clicking 'OK' you will have a new column added to your table that has a table in each row.Expand that column. Select only the Department and deselect the 'Use original column name...'
After clicking OK you should have the desired result in your table...
