The Macro That Works for One File and Fails for a Folder
A team built a standard macro that cleanses a single Excel file: it reads the file, applies a Data Cleansing tool, a Multi-Row Formula to fill down a category, and writes a .yxdb. It works when run manually on one file. They now need to run it against a folder of 60 monthly files, each with a different sheet name (e.g., Jan_2023, Feb_2023), and the sheet name is not predictable from the file name. The macro currently has the file path hard-coded in the Input Data tool. The team wants a single workflow that processes all 60 files and produces one combined output, and they want to be able to reuse the cleansing logic later on a different folder.
1. Should this be a standard macro, a batch macro, or an iterative macro? Justify the choice and describe the control parameters.
Read the worked answer
A batch macro is the right choice. The task is: for each of 60 files, run the same cleansing logic independently, then combine results. A standard macro cannot loop over inputs; an iterative macro is for cases where each iteration depends on the previous iteration's output (e.g., convergence), which is not the case here. The batch macro should expose two control parameters: the full file path and the sheet name. The Input Data tool inside the macro is replaced by a control-parameter-driven input: the file path comes from the control parameter, and the sheet name is passed via a second control parameter wired into the Input Data tool's sheet selection (or via a Dynamic Input tool configured with the file path and sheet name). The macro's output is the cleansed rows. The outer workflow uses a Directory tool to list the 60 files, then a Formula to derive the sheet name (or a lookup table mapping file to sheet), and feeds those rows into the batch macro's control parameter. The macro's output is unioned automatically by the batch macro, producing one combined output. Reusability: the same macro can be pointed at a different folder by changing the outer Directory tool's path, and the cleansing logic is unchanged.
2. The Multi-Row Formula inside the macro fills down a category, but after batching, categories leak across files (January's last category fills into February's first row). Diagnose and fix.
Read the worked answer
Multi-Row Formula operates on the stream in order and looks at the previous row. When the batch macro processes files independently, each file's stream should be isolated-but if the outer workflow unions all files before the Multi-Row Formula, or if the macro's internal stream is not grouped by file, the fill-down crosses file boundaries. The fix depends on where the formula lives. If it is inside the batch macro, each iteration sees only one file, so leakage should not occur unless the macro is not actually batching (e.g., the control parameter is not wired, so it processes one combined input). If the formula is outside the macro after a Union, add a Group By on a file identifier (e.g., a SourceFile field added before the union) in the Multi-Row Formula so fill-down resets per file. Also ensure the data is sorted by the intended order within each group before the Multi-Row Formula, since it relies on row order. Verify by checking the first row of each file's output for a null or correct category rather than the previous file's last value.