Cleaning up Data after an Unpivot

O

OldCityCat

New member
Joined
Apr 24, 2017
Messages
6
Reaction score
0
Points
0
Location
St Augustine FL
Excel Version(s)
2016
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
 

Attachments

  • qryTimeSheet.xlsx
    29.2 KB · Views: 14
Couldn't do it all in the UI, so here is the M code

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
 
Last edited:
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.
 
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
 
OldCityCat said:
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.
Click to expand...

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.

OldCityCat said:
Need to make preparations at my office and home for possible evacuation, due to Dorian.
Click to expand...

Hadn't heard about Dorian, good luck with it.
 
You must log in or register to reply here.
Back
Top