Power Query showing 3000 columns in my "TransformFile" table

[Guitarmageddon]

Hello everyone. I have a query with a rather large amount of applied steps, which is applying to a large number of individual files. Sometimes, it can take as long as 30 minutes to go through all the steps. Knowing what I know now about PQ, i would probably do things a bit differently, but I suppose thats a story for a different day.

When going through making some changes for the process owner, I realized that in the "Transform File" column before expanding all my data sources, excel is seeing there are 3000 columns in each file. Is this normal behavior? I wonder if this is also adding to my processing time? As you can see below, here is a small sample of the individual files, and when I click into the table data, the preview shows it going all the way to 3000 columns.

And here, out to 3000 columns. I only have data within the first 6 of any of these files.

And once you expand that column, it only does find my 6 columns. Is this much about nothing and just something that power query shows you upon the import?

For what its worth, heres what advanced editor shows for the first several steps involving the import etc

 

[RoryA]

Your table expansion step specifies only 6 columns, so that's what you get. The most likely cause of the 3000 is that the workbooks have a deemed 'used range' greater than is actually the case - often happens when workbooks are manipulated to remove a load of data, or someone adds formatting to a cell somewhere way beyond the actual data by mistake.