•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Pulling out a single column and then a new column containing the value from a single cell in power query from a folder of files
Our take
To extract specific data from a folder of Excel files using Power Query, you can efficiently gather key values while including a unique report date from each file. By targeting the desired column and the specific cell containing the report date, you can structure your output to display the file name alongside the first column's values and the report date from cell 4 of column 3. This approach simplifies your data management and ensures you have a clear view of essential information across multiple files.
I have a folder of excel files which are all the exact same format. Sheet one has a bunch of information all across columns and rows. The format is key values (names) and then other stuff but importantly dataset specific values at the top (in this case im looking for the date the report was filed). I have tried to pull the information out and can pull the columns out but I am struggling to understand if its even possible to do what Im trying to do.
My output from my query should be for each file: file name, column 1, and then a second column that is ONLY the value taken from column 3 cell 4 (report date). So what I will end up with is column 1 and next to each entry in column 1 the value from column 3 cell 4 which will be the same for each separate file but different between files.
If this is the wrong sub for this please redirect me, thanks.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- I need to return the data from a cell, but the cell to identify where the data is stored movesSo I have the below data returned from a time clock in system. Ill be pulling this data in to a master sheet from a folder, and then subsequently pulling in the newest report stored into the folder there after. I need to be able to return in a separate sheet the id number accompanied by the total hours for the individual. So it would read like this. ID HRS 3 15 4 28.45 5 41.77 The Total Hours column row and ID row will differ report to report due to clocking in and out for breaks and not so no way to hard code the data to a cell. Im not amazing with power query and wondered if that would be able to solve my issues but i cant think of any excel function that will be able to do what i require it to do. Any help https://preview.redd.it/0hz7vh5yaksg1.png?width=748&format=png&auto=webp&s=a1cda5e0e088a18f37ca82eb3c14122becd8659c submitted by /u/Unlikely-Two-8412 [link] [comments]
- Power query multiple workbooks and sheetsI am trying to combine multiple workbooks that have multiple sheets but I am missing a step. Get data, from file, from folder and open. Transform. Add custom column (=Excel.Workbook([Content]). I select data, item and kind from the custom drop down. Then I filter item to select the specific sheets I want. Close and load. When I do this, I only get the filepaths of the sheets I selected. I tried the same steps, but I clicked on the "Table" link in the custom data column before close and load. It brings up the data for that worksheet and it's correct. But when I then close and load, it will bring up the actual data, but only for that worksheet that I looked at. How do I get the *data* from all the worksheets that I selected in custom data to load. Or am I completely off base? Thanks! This is my first foray into power query and it's a bit daunting. submitted by /u/Choice_Intention_778 [link] [comments]
- Power query for a large datasetMy company uses a horrible format for its daily production sheets, but the data can be pulled through power query. I want to build a reporting tool for looking at any major trends that are currently missed. Ideally looking at part efficiency by machine type and some other descriptive data too like efficiency by shift manger etc. My problem is that even after cutting unnecessary columns and filtering unnecessary rows, it takes forever to load anything. ChatGPT isn’t all that helpful, I’d like some expert advice please! For info, rough number of rows of data is about 50,000 per year. I want to cover at least the last three years. Sheets are all saved into a folder by month, within a folder by year. submitted by /u/CanJesusSwimOnLand [link] [comments]
- Combining several files into one, matching by date and timeHello fellow excel nerds! I need some help I currently have data coming from several different sensors in several different csv files. Each operates on its own schedule and measures different variables. I’d like to combine them all into one master sheet, where for each time and date I have the output from each sensor. I’m sensing power query is going to be what helps me, but I haven’t quite sussed it out yet. Part of the difficulty is that the columns are named differently as the sensors measure different things (like luminosity, speed, humidity, location), and, of course, they operate on different timelines. So for example one sensor will measure every minute, another every hour, and yet another measures only when specific events happen so those timestamps are basically random. What I’m trying to get is a master table that would tell me at 9pm, the speed was x, the humidity was y, the location was z. I understand I will have a lot of blank values at timestamps where only one or two sensors recorded data points, but I can live with that. Does anyone know how to do this, or even know the accurate term for this manipulation to help me in my research? Many thanks! submitted by /u/Mysterious_Camel_717 [link] [comments]