How to split columns into different rows (almost like one-hot encoding?) in Excel?

Question

I've started doing payroll for a new company, and they are currently doing it very manually, and I know there must be a better way! Our employees can work Standard Time, Overtime, and Bonus time that all show on one row through our timekeeping export, but I believe I will need to split each employee row into three rows, as there are three different job codes for the different Time codes. For example, one employee could work 20 hours in our office (Job code of REG), and 20 hours in our warehouse (job code of WRH), and then could do Overtime in either or both (Different job codes of OT and OWH), and also have Bonus time in either (All Bonus time is coded as BTM).

I've been racking my brain on how to make this work, but am stuck. I've been messing with pivot tables, and have some PowerQuery experience too if I need to go that route. I have attached an example of what it looks like, and how I'd like it to look. Thank you all for your help!!

Example

Answer 1

If you needed to do this in powerquery

Load your data and titles into powerquery (data .. from table/range [x] columns)

Click select the Employee Name, Job Code, Rate and OT Rate columns, right click, unpivot other columns

Add column .. custom column .. with column name Custom and formula

= [Job Code]&"-"&[Attribute]

Right click and remove job code column

Add column index column

Right click Custom column and replace REG-ST Hours with REG
Right click Custom column and replace REG-OT Hours with OT
Right click Custom column and replace REG-Bonus Hours with BTM
Right click Custom column and replace WRH-ST Hours with WRH
Right click Custom column and replace WRH-OT Hours with OWH
Right click Custom column and replace WRH-Bonus Hours with BTM

Click select attribute column, Transform... pivot column ... and use Value column as Value

Sort on index column

Right click and remove index column

Drag columns to sort as desired

File ... close and load

Sample full code based on sample data, that can be loaded into home .. advanced editor ...

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Name", type text}, {"Job Code", type text}, {"ST Hours", Int64.Type}, {"OT Hours", Int64.Type}, {"Bonus Hours", Int64.Type}, {"Rate", Int64.Type}, {"OT Rate", type number}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee Name", "Job Code", "Rate", "OT Rate"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Job Code]&"-"&[Attribute]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Job Code"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index","REG-ST Hours","REG",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","REG-OT Hours","OT",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","REG-Bonus Hours","BTM",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","WRH-ST Hours","WRH",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","WRH-OT Hours","OWH",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","WRH-Bonus Hours","BTM",Replacer.ReplaceText,{"Custom"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value5", List.Distinct(#"Replaced Value5"[Attribute]), "Attribute", "Value", List.Sum),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in #"Removed Columns1"