Hi @QZ ,
You can accomplish this by creating a Power Query function to process each Excel file and then applying that function across all files located in a specific folder. The process involves handling the unique two-row header structure and extracting the "Category" and "Pattern" metadata from each file before unpivoting the data into a summarized format.
Here is the complete M code to achieve this. You can paste this solution directly into the Advanced Editor of a blank query in either Excel or Power BI.
let
// 1. Point to the folder containing your Excel files
Source = Folder.Files("C:\YOUR\FOLDER\PATH"),
// 2. Filter for Excel files only (optional but recommended)
FilterExcelFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx") or Text.EndsWith([Name], ".xls")),
// 3. Define the function to transform each file
TransformFile = (binaryContent as binary) =>
let
// Load the workbook and navigate to the first sheet
Source = Excel.Workbook(binaryContent, null, true),
Sheet1 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
// Extract Category and Pattern from their specific cells
// Note: {4} is row 5, {Column9} is column I; {Column15} is column O. Adjust if your layout is different.
CategoryValue = try Sheet1{4}[Column9] otherwise null,
PatternValue = try Sheet1{4}[Column15] otherwise null,
// Prepare the main data table (skip top rows to get to the headers)
DataTable = Table.Skip(Sheet1, 6),
// Handle the two-level headers by transposing, filling, merging, and transposing back
Transposed = Table.Transpose(DataTable),
FilledDown = Table.FillDown(Transposed, {"Column1"}),
MergedHeaders = Table.CombineColumns(Table.TransformColumnTypes(FilledDown, {{"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"MergedHeaders"),
TransposedBack = Table.Transpose(MergedHeaders),
// Promote the newly created first row to be the headers
PromotedHeaders = Table.PromoteHeaders(TransposedBack, [PromoteAllScalars=true]),
// Unpivot the stage columns, keeping the identifier columns
UnpivotedData = Table.UnpivotOtherColumns(PromotedHeaders, {"Checker", "Machine no.", "S/C", "FB"}, "Stage", "Value"),
// Filter out any rows where the value is null (i.e., no data was entered)
FilteredRows = Table.SelectRows(UnpivotedData, each [Value] <> null),
// Add the extracted Category and Pattern as new columns
AddCategory = Table.AddColumn(FilteredRows, "Category", each CategoryValue),
AddPattern = Table.AddColumn(AddCategory, "Pattern", each PatternValue)
in
AddPattern,
// 4. Invoke the custom function on each file's content
InvokeCustomFunction = Table.AddColumn(FilterExcelFiles, "Transform File", each TransformFile([Content])),
// 5. Remove other columns, keeping only the transformed data
RemoveOtherColumns = Table.SelectColumns(InvokeCustomFunction, {"Transform File"}),
// 6. Expand the table column to get the final result
ExpandedData = Table.ExpandTableColumn(RemoveOtherColumns, "Transform File", {"Checker", "Machine no.", "S/C", "FB", "Stage", "Value", "Category", "Pattern"}),
// 7. Reorder columns to match the desired output
ReorderedColumns = Table.ReorderColumns(ExpandedData,{"Checker", "Machine no.", "S/C", "FB", "Category", "Pattern", "Stage", "Value"}),
// 8. Set the correct data types for the final columns
ChangedType = Table.TransformColumnTypes(ReorderedColumns,{{"Checker", type text}, {"Machine no.", type text}, {"S/C", type text}, {"FB", Int64.type}, {"Category", type text}, {"Pattern", type text}, {"Stage", type text}, {"Value", Int64.type}})
in
ChangedType
To use this code, first open Power Query by getting data from a folder (Data > Get Data > From File > From Folder in Excel). Point it to the folder containing your source files and click Transform Data. In the Power Query Editor, open the Advanced Editor from the Home tab. Delete any existing text and paste in the M code provided. The most important step is to update the folder path on the third line of the code to match your folder's location. After pasting and updating the path, click Done, and Power Query will process all the files and display the final summary table.
The code works by first using the Folder.Files function to get a list of all files. The core logic is contained within a custom function named TransformFile, which is designed to process one file at a time. Inside this function, it extracts the Category and Pattern values from their specific cell locations. It then cleverly handles the two-level column headers by transposing the table, filling down the stage names, merging the header rows into one, and transposing back. Following this, the Table.UnpivotOtherColumns function converts the wide stage columns into a long format. Finally, the extracted Category and Pattern are added as new columns to the resulting data. The main query then applies this function to every file, combines the results into a single table, and performs final cleanup like reordering columns and setting data types.
Best regards,
