Forum Discussion
Copy data from one workbook to another. then delete the original data
The screenshot is very close to that given by working versions.
One thought is that the unit of interaction between PowerQuery and Excel is the Table. I think it may be possible work with Named Ranges or the UsedRegion of a Sheet but they are not the preferred options.
Unless you have already done it, I would suggest going through each of your four files and converting the data to a Table [Ctrl/T]. This will give a default name of Table1 but it can be renamed to give some more meaningful reference such as 'EngineeringTask' (the same name in each file). Then, within the Combine & Edit dialogue, select the Table name to be the basis for extracting data from the sample file.
Fingers crossed this takes us a step forward!
Peter, thanks for your reply. I have tried your described method but I seem to be struggling.
I have one file Master.xlsm on D:\
I have four files Engineer1 thro Engineer4 on D:\Test\
I go to data ribbon and select New Query\From File\ From Folder and set the path to D:\Test
the query selects the 4 files and I press Combine and load.
THe combinne dialogue window appears and I select Example file as Engineer1. The prview pane show me the layout which is fine.
I press the OK button and the query starts running.
I then get a error message
[Expression.Error] The key didn't match any rows in the table..
Please see attached screenshot
What am I doing wrong please
VonryanPeterBartholomew1
The screenshot is very close to that given by working versions.
One thought is that the unit of interaction between PowerQuery and Excel is the Table. I think it may be possible work with Named Ranges or the UsedRegion of a Sheet but they are not the preferred options.
Unless you have already done it, I would suggest going through each of your four files and converting the data to a Table [Ctrl/T]. This will give a default name of Table1 but it can be renamed to give some more meaningful reference such as 'EngineeringTask' (the same name in each file). Then, within the Combine & Edit dialogue, select the Table name to be the basis for extracting data from the sample file.
Fingers crossed this takes us a step forward!
PeterBartholomew1 , From Folder connector works fine with entire sheets. The only point data for all files in the folder have to have the same structure.
Thank you. From the selections available on the combine files menu, I suspected that was the case. I wouldn't have personal experience because I haven't used location (Sheet and cell notation) to reference data objects for a number of years now. Tables are simply a more natural starting point for me and, most recently, this is followed by spilt arrays in which only the anchor cell is named.
Oddly enough, it is sometimes difficult to establish a dialogue with Excel users; wonder why that is? ;-)
That could be tables. With them even easier. But with that all your files are to be with exactly the same Table1 with exactly the same column names. Only values could be different. What is done, you take any file in the folder as a sample one, transform it (it could be Tables, Named ranges or entire sheet) and generated for such transformation function is applied to all files in the folder. If files have different structure you failed.
Oh, Excel users are quite different. Even if speak about the Excel, sometimes we speak on different languages. That's not good and not bad, that as it is.
