There is a moment every Power Query beginner knows: the workbooks are combined, the custom column is added, and then the whole thing collapses into a list of file paths instead of the data you actually need. That is exactly where this user landed, and the frustration is entirely justified. The fix, however, is not hidden in some advanced setting or a secret formula. It is a matter of understanding what Power Query is actually doing when you expand that custom column.
The issue here is a classic case of expansion without direction. When you add `=Excel.Workbook([Content])` and then choose the data, item, and kind columns, you are creating a structured column that holds the promise of your sheets. But a promise is not the same as the delivery. If you close and load at that stage, Power Query dutifully gives you the metadata, the file paths, and the sheet names, because that is all you have explicitly asked for. The data itself is still sitting inside that nested Table values, waiting for you to take the next step. Clicking on the Table link to preview it is not enough. You have to expand the column properly, using the expand icon in the column header, and then select the columns you want to bring into your final table.
What this user discovered by accident, clicking into one Table and seeing the correct data, is the key to the whole puzzle. That preview is the data, but it is only the data for that one sheet. The reason they only got that one sheet loaded is that they had effectively drilled down into a single record. The path forward is to expand the entire column at once, not to open one table in isolation. When you expand the custom column and choose the relevant columns, Power Query will pull the data from every sheet across every workbook that matches your filter. It is not an extra step; it is the step that makes the entire process work.
This is not a failure of the tool. It is a failure of expectation, and it is a very common one. The good news is that once you see it, you cannot unsee it. The pattern is simple: add the custom column, filter your items, then expand the column to bring the data into the table. That expansion is what turns a list of references into a usable dataset. For anyone stuck at this exact point, the answer is not to abandon Power Query or to manually copy and paste across files. It is to trust that the data is there, nested inside that column, and to use the expand button to pull it out. That is the simple fix, and it will save you hours of frustration.