Log In
Register
Home
March 10, 2016
Offline
Hi, I have an invalid file of 300 sheets. Each sheet shows attendance hours of employees per month.
I need to consolidate everything into one table.
In the file I attached here -
There are 2 employee sheets -
Bnei David works in one place and Dani Moshe works in 2 places.
I need to arrange the tables so that they are accepted
into one organized central table as in the File is correct sheet that I made manually.
In Power Query you need to import from a folder -
and load a folder in which there will be 12 files
of working hours (each month a separate file) each file contains 300 sheets
You need to run a fixed query code on one sheet and run it on all 300 sheets.
Thank you very much for your help!!
This is the correct file - Problem-Table-1.xlsx
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Lea,
Looks bad enough 🙂
You still need a solution or you found it?
New Member
July 11, 2020
Offline
I have build a solution for you. Interesting problem! See attached.rnrnI built a function to convert 1 filernCalled the function for all files in folder (you need to customize it for your situation)rnrnThe core of the solution is this function (see comments for some explanation):rnrn(Source) =>rnlet#"Kept Last Rows" = Table.LastN(Source, 1), // The employee datails#"Removed Other Columns" = Table.SelectColumns(#"Kept Last Rows",{"Column1"}),#"Employee Details Table" = Table.RenameColumns(#"Removed Other Columns",{{"Column1", "Name"}}),#"Back to Source for Job List" = Source,#"Kept First Rows" = Table.FirstN(#"Back to Source for Job List",1), // the row with the job names#"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Column1"}),#"Transposed Table" = Table.Transpose(#"Removed Columns"), // Put the job names in the first column#"Filtered Rows" = Table.SelectRows(#"Transposed Table", each [Column1] <> null), // rmoved empty rows to keep job names#"Jobs List" = Table.ToList(#"Filtered Rows"), // turned it into a list for easy reference by position#"Back to Source for final" = Source, // For the actual timeshteet data#"Removed Top Rows" = Table.Skip(#"Back to Source for final",1),#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",3),#"Transposed Table1" = Table.Transpose(#"Removed Bottom Rows"), // Transpose#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), // Dates as headers#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1, Int64.Type), // Give rows a number#"Added Custom" = Table.AddColumn(#"Added Index", "Job", each #"Jobs List"{Number.IntegerDivide([Index],5)}), // find the right job by position#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Job", "Column1"}, "Attribute", "Value"), // Get the timesheet entries, but turn the date a a row in the table for each Job/Type of entry#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Column1]), "Column1", "Value"), // turn the type of entry into columns#"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Employee Details", each #"Employee Details Table"), // add the employee details extracted earliers#"Expanded Employee Details" = Table.ExpandTableColumn(#"Added Custom1", "Employee Details", Table.ColumnNames(#"Employee Details Table"), Table.ColumnNames(#"Employee Details Table")),#"Renamed Columns" = Table.RenameColumns(#"Expanded Employee Details",{{"Attribute", "Date"}}),#"Date as Date Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),#"Reordered Columns" = Table.ReorderColumns(#"Date as Date Type",{ "Date", "Job","Name", "Entrance", "Output"})in#"Reordered Columns"rn
1 Guest(s)
