Excel Power Query: transform two cells into two duplicate rows

Question

I have an Excel Power Query that imports and transforms a CSV data-transfer file from what is essentially a timesheeting web application ready for importing into our payroll application, again via CSV. Certain rows have both ‘daytime’ and ‘night-time’ hours in two separate fields. I need to transform these two fields into two separate rows adding a custom ‘rate’ field for each. To illustrate:

Name               day hours      night hours
A.N Other               6              4

Transforms into:

Name               day hours      night hours    hours      rate
A.N Other               6              0           6        rate1
A.N Other               0              4           4        rate2

The payroll application can only process ‘hours’ and ‘rate’ on individual rows
I have gone through the full ‘M’ specification and nothing jumps out.
I have googled and there’s lots of stuff about removing duplicates, not a lot about creating them!

To be honest, I don’t really know where to start. Any help would be warmly received.

Alexis Olson Alexis Olson · Accepted Answer · 2016-07-28T14:46:20

The key step here is to select the day hours and night hours columns in your query editor and choose Unpivot Columns under the Transform tab.

This will yield:

Name                Attribute      Value       
A.N Other           day hours        6
A.N Other          night hours       4

Once you do that you can create custom columns using the following rules:

if [Attribute] = "day hours" then [Value] else 0

and analogously for the night hours column:

if [Attribute] = "night hours" then [Value] else 0

From there you can rearrange, rename, and delete columns as desired.

You can also create a custom rate column with similar logic using the Attribute column or one of the hours columns.

Excel Power Query: transform two cells into two duplicate rows

1 Answers