Moving the totals of a column to a single row

J

jazzista

Member
Joined
Jan 4, 2017
Messages
50
Reaction score
0
Points
6
Excel Version(s)
Office 365
Hello. I have a table that I have managed to unwind the data with a simple function. Now, the challenge is that I need that all the 31 dollar charges that are in column U (ADMIN FEE) to be added to a specific row under ID D15334 on the HUM department and keeping column U (ADMIN FEE) with zeros. See file attached. I have also highlighted the column and the specific row where the transformation needs to happen. Thanks in advance for all the help.
 

Attachments

  • file to transform.xlsx
    160.4 KB · Views: 28
Please provide an example of desired output
 
desired result of the columns moving to rows

This is the file that contains the desired result along with a pivot table. Thanks in advance for all the help
 

Attachments

  • desired table result.xlsx
    109.1 KB · Views: 14
Last edited by a moderator:
See attached. Change your function as follows

Code: 
(Cleanpayrollfile)=>
let
    Source = Excel.CurrentWorkbook(){[Name=Cleanpayrollfile]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Employee ID", type text}, {"Gross Wages", type number}, {"EE Expense Reimb", type number}, {"FICA", type number}, {"Column14", type any}, {"FUTA", type number}, {"SUTA", type number}, {"EE Benefits", type number}, {"Life Ins", type number}, {"EAP", Int64.Type}, {"401K Match", type number}, {"Admin Fee", Int64.Type}, {"EE Setup Fee", Int64.Type}, {"Loan", Int64.Type}, {"LTD", type number}, {"STD", type number}, {"Imputed Income", type number}, {"Total Billed", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Department Name"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column3", "Column2", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Employee ID] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Total Billed", "Column14"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Department Name", "Employee ID", "Gross Wages", "FICA", "FUTA", "SUTA", "EE Expense Reimb", "EE Benefits", "Life Ins", "EAP", "401K Match", "Admin Fee", "EE Setup Fee", "Loan", "LTD", "STD", "Imputed Income"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Gross Wages", Currency.Type}, {"FICA", Currency.Type}, {"FUTA", Currency.Type}, {"SUTA", Currency.Type}, {"EE Expense Reimb", Currency.Type}, {"EE Benefits", Currency.Type}, {"Life Ins", Currency.Type}, {"EAP", Currency.Type}, {"401K Match", Currency.Type}, {"Admin Fee", Currency.Type}, {"EE Setup Fee", Currency.Type}, {"Loan", Currency.Type}, {"LTD", Currency.Type}, {"STD", Currency.Type}, {"Imputed Income", Currency.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Department Name", "Employee ID"}, "Attribute", "Value"),
//New Code
    Part1= Table.SelectRows(#"Unpivoted Other Columns" , each ([Attribute] <> "Admin Fee")),
    #"Filtered Rows2" = Table.SelectRows(#"Unpivoted Other Columns" , each ([Attribute] = "Admin Fee")),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows2",{"Department Name", "Employee ID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns2", "Department Name", each "HUM - HUM"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Employee ID", each "D15334"),
    Part2 = Table.ReorderColumns(#"Added Custom1",{"Department Name", "Employee ID", "Attribute", "Value"}),
    Combined=Part1&Part2
in  Combined

Filter for anything that is not an admin fee, and save as Part 1
Filter for anything that is an admin fee, create two columns where Department=HUM-HUM and ID-D15344
Put both parts together
Change output from table to pivot report. Sample in file

View attachment file to transform-1.xlsx
 
Or, come to think of it, you could just add two new columns and erase the old ones
Code: 
(Cleanpayrollfile)=>
let
    Source = Excel.CurrentWorkbook(){[Name=Cleanpayrollfile]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Employee ID", type text}, {"Gross Wages", type number}, {"EE Expense Reimb", type number}, {"FICA", type number}, {"Column14", type any}, {"FUTA", type number}, {"SUTA", type number}, {"EE Benefits", type number}, {"Life Ins", type number}, {"EAP", Int64.Type}, {"401K Match", type number}, {"Admin Fee", Int64.Type}, {"EE Setup Fee", Int64.Type}, {"Loan", Int64.Type}, {"LTD", type number}, {"STD", type number}, {"Imputed Income", type number}, {"Total Billed", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Department Name"}}),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Top Rows",{"Column3", "Column2", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Employee ID] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Total Billed", "Column14"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Department Name", "Employee ID", "Gross Wages", "FICA", "FUTA", "SUTA", "EE Expense Reimb", "EE Benefits", "Life Ins", "EAP", "401K Match", "Admin Fee", "EE Setup Fee", "Loan", "LTD", "STD", "Imputed Income"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Gross Wages", Currency.Type}, {"FICA", Currency.Type}, {"FUTA", Currency.Type}, {"SUTA", Currency.Type}, {"EE Expense Reimb", Currency.Type}, {"EE Benefits", Currency.Type}, {"Life Ins", Currency.Type}, {"EAP", Currency.Type}, {"401K Match", Currency.Type}, {"Admin Fee", Currency.Type}, {"EE Setup Fee", Currency.Type}, {"Loan", Currency.Type}, {"LTD", Currency.Type}, {"STD", Currency.Type}, {"Imputed Income", Currency.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Department Name", "Employee ID"}, "Attribute", "Value"),

    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Department Name2", each if [Attribute]="Admin Fee" then "HUM - HUM" else [Department Name]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Employee ID2", each if [Attribute]="Admin Fee" then "D15334" else [Employee ID]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Department Name", "Employee ID"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Department Name2", "Department Name"}, {"Employee ID2", "Employee ID"}})
in #"Renamed Columns2"
 
Or just transform the two columns

Code: 
...
 #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Department Name", "Employee ID", "Gross Wages", "FICA", "FUTA", "SUTA", "EE Expense Reimb", "EE Benefits", "Life Ins", "EAP", "401K Match", "Admin Fee", "EE Setup Fee", "Loan", "LTD", "STD", "Imputed Income"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Gross Wages", Currency.Type}, {"FICA", Currency.Type}, {"FUTA", Currency.Type}, {"SUTA", Currency.Type}, {"EE Expense Reimb", Currency.Type}, {"EE Benefits", Currency.Type}, {"Life Ins", Currency.Type}, {"EAP", Currency.Type}, {"401K Match", Currency.Type}, {"Admin Fee", Currency.Type}, {"EE Setup Fee", Currency.Type}, {"Loan", Currency.Type}, {"LTD", Currency.Type}, {"STD", Currency.Type}, {"Imputed Income", Currency.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Department Name", "Employee ID"}, "Attribute", "Value"),

TransformedColumn = Table.FromRecords(Table.TransformRows(#"Unpivoted Other Columns" ,(r) => Record.TransformFields(r,{"Department Name",each if r[Attribute] = "Admin Fee" then "HUM - HUM" else r[Department Name]}))),
TransformedColumn2 = Table.FromRecords(Table.TransformRows(TransformedColumn ,(r) => Record.TransformFields(r,{"Employee ID",each if r[Attribute] = "Admin Fee" then "D15334" else r[Employee ID]})))
in TransformedColumn2
 
Hi: ​
horseyride
! Thanks for the amazing code. I will try on the PR query and hopefully it would work. Thanks again for the help. ​
 
You must log in or register to reply here.
Back
Top