Results 1 to 6 of 6

Thread: Cleaning up Data after an Unpivot

  1. #1
    Neophyte OldCityCat's Avatar
    Join Date
    Apr 2017
    Location
    St Augustine FL
    Posts
    4
    Articles
    0
    Excel Version
    2016

    Cleaning up Data after an Unpivot



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Conjurer Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    119
    Articles
    0
    Excel Version
    Excel 365
    Hi Old...Cat ;-)
    Check my attachment.
    Attached Files Attached Files

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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 by Bob Phillips; 2019-08-28 at 07:29 PM.

  4. #4
    Neophyte OldCityCat's Avatar
    Join Date
    Apr 2017
    Location
    St Augustine FL
    Posts
    4
    Articles
    0
    Excel Version
    2016
    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.

  5. #5
    Neophyte OldCityCat's Avatar
    Join Date
    Apr 2017
    Location
    St Augustine FL
    Posts
    4
    Articles
    0
    Excel Version
    2016
    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

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by OldCityCat View Post
    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.

    Quote Originally Posted by OldCityCat View Post
    Need to make preparations at my office and home for possible evacuation, due to Dorian.
    Hadn't heard about Dorian, good luck with it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •