1. Transformation types in Excel Power Query
There are many different options available for column transformations in the Power Query Editor, and many of them can be done right here in the ribbon with guided point and click steps.
You will notice that there are many column transformation options that are available both in the ‘Transform’ tab as well as the ‘Add Column’ tab. Based on your task and your desired output you may choose to take either approach.
Transform, essentially means you will take an existing column and replace it with the transformed step. For instance, let’s say we want to convert the dose_unit column to lowercase, since we can see that there is a mix of upper and lowercase values.
If we stick with the transform tab of the ribbon, and select Format lowercase, it will overwrite the existing column. However, had we selected it under the ‘Add column’ tab, it means that Power Query will generate a new column with our desired change, in this case lowercase version. Our original column remains in place.
Let’s go back to our original step and overwrite the original column. A handy tip here is you can also get many of the transform column options by right-clicking on a column header. In this case, right click dose_unit, then under Transform, select ‘lowercase’.
Power Query also makes it easy to do numeric transformations, for instance, say we wish to convert patient_weight_kg to pounds. This will require multiplying the column value by 2.2 for all rows. The ‘Number Column’ options under the transform tab include many standard options, including multiplication. Clicking on ‘Multiply’ brings up a dialog box which allows us to enter in a specific constant value. Now all rows have multiplied by 2.2 and the value is converted to pounds. Let’s update the field name to reflect this change.
It’s also possible to reference multiple columns to do numeric calculations. Let’s look at an example in the dailycensus query. Say we wanted to divide one column by another, specifically patient days divided by budgeted beds. This will give us a sense of how many beds were occupied on each day. Holding control (or command in Mac OS) and clicking the numerator followed by the denominator, then Divide under the Add Column > From Number > Standard options gives us that calculation. We can convert to percentage and now see our daily occupancy rate.
Many date/time transformations also come in handy, for instance, we can choose to Add Column of the month name for medication_administration_date. Now we have a new column with the month name in Text format. We can take it a step further and do a Text transformation step to extract the first 3 characters. Now we have a short-form month which is a little bit more concise.
Power Query also has options for dealing with timestamps. For instance, if you do not need the information to the minute or second level, you can convert a Time field to just the start of the hour.
Let’s see how this looks for medication_administration_time. Under Transform let’s update the value to the start of the hour. Now we have all the timestamps rounded that can enable an analysis of hourly activity.
Okay now it’s your turn!
2. Let's practice!