Cleaning up Data after an Unpivot

[OldCityCat]

In my attempt to stream one of our payroll process with Power Query.
Attached is a workbook with a sample of the raw data along with sample of how I need it transformed.
I have made s few attemps but I am too new at PQ to know if it is the right tool for this job.

Thank you in advance from any and all suggestions.

OldCityCat

 

[Bill Szysz]

Hi Old...Cat ;-)
Check my attachment.

 

[Bob Phillips]

Couldn't do it all in the UI, so here is the M code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    totalscol.Remove = Table.RemoveColumns(Source,{"Totals"}),
    headings = Table.ColumnNames(totalscol.Remove),
    dateheadings = List.LastN(headings, List.Count(headings)-3),
    absent.ToNull = Table.ReplaceValue(totalscol.Remove,"Absent",null,Replacer.ReplaceValue, dateheadings),
    space.ToNull = Table.ReplaceValue(absent.ToNull," ",null,Replacer.ReplaceValue, dateheadings),
    data.Cleansed = space.ToNull,

    hoursindex.Add = Table.AddIndexColumn(data.Cleansed, "Index", 0, 1),
    hoursrows.Keep = Table.SelectRows(hoursindex.Add, each Number.IsEven([Index])),
    hours.Unpivot = Table.UnpivotOtherColumns(hoursrows.Keep, {"Pay Code", "Emp ID", "Name", "Index"}, "Date", "Hours"),
    hours.Finalised = Table.AddIndexColumn(Table.RemoveColumns(hours.Unpivot, "Index"), "Index", 0, 1),

    totals = data.Cleansed,
    personal.Filldown = Table.FillDown(totals,{"Name", "Emp ID", "Pay Code"}),
    totalsindex.Add = Table.AddIndexColumn(personal.Filldown, "Index", 0, 1),
    totalsrows.Keep = Table.SelectRows(totalsindex.Add, each Number.IsOdd([Index])),
    totals.Unpivot = Table.UnpivotOtherColumns(totalsrows.Keep, {"Name", "Emp ID", "Pay Code", "Index"}, "Date", "Total Hours"),
    totals.Finalised = Table.AddIndexColumn(Table.RemoveColumns(totals.Unpivot, "Index"), "Index", 0, 1),
    
    hourstotals.Merge  = Table.NestedJoin(hours.Finalised, {"Index"}, totals.Finalised, {"Index"}, "totals.Finalised", JoinKind.LeftOuter),
    totalhours.Extract = Table.ExpandTableColumn(hourstotals.Merge, "totals.Finalised", {"Total Hours"}, {"Total Hours"}),
    report.Finalise = Table.RemoveColumns(totalhours.Extract,{"Index"})
in
    report.Finalise

 

[OldCityCat]

Bill,
Thank you for answering my question "if I had the right tool for the job".
Looks great!
I beleive after reviewing your queries I will learn a lot.

Thank you for all your help and the prompt response.

 

[OldCityCat]

Bob,
Thank you for taking the time to write this M Code!
It will be a few days before I will be able to take it for a spin.
Need to make preparations at my office and home for possible evacuation, due to Dorian

I will let you knoe how it goes asap

OldCityCat

 

[Bob Phillips]

Bob,
Thank you for taking the time to write this M Code!
It will be a few days before I will be able to take it for a spin.

Bill's solution and mine are much the same, he did it with 3 queries, I did it all in one. I omitted to include a break line after each person, that can easily be added using the same trick that Bill deployed.

Need to make preparations at my office and home for possible evacuation, due to Dorian.

Hadn't heard about Dorian, good luck with it.