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
Hi Lea,
Looks bad enough 🙂
You still need a solution or you found it?
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
The files with solution and examples
