Evaluate text field value and create a column based on unique occurrences

[anaib]

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.

[jgeddes · Accepted solution]

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...

[anaib]

Truly appreciate it @jgeddes .  Worked like a charm.

[jgeddes]

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"?

[anaib]

Hi @jgeddes 

Thank you for the pointer.  I will look up how to do that.  I am an infra guy 🙂