Hi,
Need some help to finish a task, I understand that the answer may be in the Query course am enrolled in but am a long way from finishing hence the request. Basically data is entered manually in rows 50 to 55 from columns B to W each shift day and night. I would like to merge the data and produce a daily count so as to keep up with stock on hand. How does one go about targeting just the required data as opposed to the whole sheet like in lesson 3.02?
Thanks
Hi Fred,
Power Query gets all of the data in a table. If you want to only get some of that data then you would use filters to exclude the old data/only include the new data. So, how would you know which rows contain the new data? Is it always rows 50-55 or will it change each day?
Another option is to use a dynamic named range for the rows and columns that you want to get, then you can get just that data using the From Excel > From File technique, which exposes data in tables, sheets and named ranges. Obviously this means that you're getting the data into a different file to the file containing the data.
I hope that points you in the right direction.
Mynda
Hi Mynda,
the data is entered into the same rows and columns on a daily sheet within a monthly work book. Meaning each shift has its own sheet and hence no data is over written. I had a example attached for context. That said, I will proceed as per your advice.
Thanks
Hi Fred,
No file attached. That said, you should use the Excel.Workbook function to get all sheets in the file excluding the sheet containing the query as shown in lesson 2.03.
Mynda
Hi Mynda,
sorry, thought the file was attached, but here we go. The attached file is very typical in the company I work for and chews up a lot of time, so any help will be greatly appreciated.
Thanks
Hi Fred,
Thanks for your file. I think it's best if you use this technique to get files from a folder. This will allow you to filter out the sheets in the file that you don't want. You also need to use this technique to number the rows in each sheet so you can specify rows 50-55 to keep.
See file attached. Note: You will need to edit the query so that it points to the file on your own PC.
Also, the data input in each sheet is not consistent. e.g. the length in some sheets is 6m or 6M or 6. This means the data in that column must be formatted as text, when you probably need it as a number. I suggest putting some data validation in those cells to force users to only enter numbers. While you're at it, you should probably do the same for the drilled, installed, grouted and plated cells.
I hope that points you in the right direction.
Mynda
