Simple data mapping with Calculated Properties (PS)

In the IT world it is very often the case that we receive data in a specific format that has to be converted into another format. The new format is needed to process the data in another system. This can be achieved with the help of PowerShell. 

While I was looking for help with mapping data from different systems, I came across the PowerShell ‘Calculated Properties’ cmdlet.

CSV files as a template

Suppose you receive data from a CSV file in the following format:

Renaming the properties

The task is to read out these users and convert them into another format by renaming some of their properties (but not all of them). More precisely, the properties should be changed like this:

• first_name should become firstName
• last_name should become lastName

While you are agonising over this task with just basic PowerShell knowledge, you already receive the next requirement:

In the target object, the “phone” and “mobile” entries should be merged to a single “telephone” property. It needs to show:

•  the value from “phone” unless it is empty
•  if “phone” is empty: the value of “mobile”, unless it is empty
•  if both are empty, “No number provided” should be entered

Summary of the requirements

Looking at the above data set, there will be the following conversions:

          1.phone: blank, mobile: 284-791-8300 →  should become telephone: 284-791-8300
          2.phone: blank, mobile: blank → should become telephone: “No number provided”
          3.phone: 822-549-3376, mobile: 591-571-6261 → should become telephone: 822-549-3376
          4.phone: blank, mobile: 626-743-0389 → should become telephone: 626-743-0389
          5.phone: 963-597-9730, mobile: 935-581-8254 → should become telephone: 963-597-9730

But how do you solve this task? By reading the Active Directory FAQ Blog!

So, here it is: one solution that solves both problems in a very simple way. I am introducing: calculated properties.

Calculated Properties

Calculated properties are implemented as hashtables. They often come together with the Select-Object cmdlet. In the following example, I only select the first record of the CSV file and convert “first_name” into “firstName”.

Here is the crucial part:

@{Name=”firstName”;Expression={$_.first_name}}

The “Name” key specifies what the property of the source object (first_name) will be called in the target object (firstName). Next, the “Expression” key is a scriptblock that determines or calculates the content that belongs to the new property.

That’s all in the case above. To sum up, the new name “firstName” will be mapped to the data of the old property “first_name”.

In the output, you only see the firstName, because we only selected this property with -Property: 

As already indicated, the Expression block can also be used for calculations or scripts. This gives us all we need to generate the phone numbers:

Furthermore, the Expression scriptblock checks quite simply whether phone has a value. If a value is available, this is set by default, otherwise it checks whether mobile has a value. If this is the case, the mobile number will be used and if both have no value, the message “No number provided” will be displayed.

PS: Do not forget to return the set value at the end.

The complete script

Finally, here is the whole script:

Useful explanations of Select-Object and -Property

• Select-Object executes commands on all data sets and returns a new list of objects.
• -Property defines which properties you want to have in the target object.
• The ` character allows PowerShell to write a command across multiple lines.
• I put id, email and city between the calculated properties without converting them to another name (if you do not need these properties in the target object, you can simply omit them).

The PowerShell output – applicable to thousands of users

As you can see we reached our goal. And the good news is that the solution can be scaled up. It would work the same way for 1,000 users, or more.

FirstAttribute already used a similar script for one of our customers who had several 10,000 users. Specifically, the project was about synchronising data between a read-only directory and an AD LDS. The latter served as a database for our FirstWare IDM-Portal. 

IDM-Portal offers a variety of helpful functionalities, such as self service and delegation features. These allow for data to be added and updated directly by the end user or relevant departments.

Conclusion

Calculated Properties can be used to implement complex data mapping operations with the help of PowerShell. At the beginning, the syntax takes getting used to, however you should be willing to accept this for the extra flexibility gained.