How to use power query with a few different sheets
Our take
I have limited experience with Query but I keep dabbling in it and like it so far. I am working on something that I believe power query would be ideal but I don't know how to start.
I download from company software to excel a "sheet" that lists all the money market funds our clients hold individually. From here I ungroup rows, convert the account #'s to number format and delete a group of accounts that hold a specific money market.
Next I download an excel sheet (CSV) from our custodian bank that lists all our clients with their respective money market fund holding and anyone who has CASH (not in a money market). On this sheet I: Sort the Core Sweep column from 'A to Z' and remove all funds except CASH. Next I create a helper column of the Account #'s, get rid of the dashes in the account numbers, and copy and paste values over it.
I then go back to the company excel sheet and create an XLOOKUP formula that looks for the account # in custodian sheet and returns the CASH amount (which tells me the dollar amount of the fund I need to buy). I then copy and paste the account #, money market fund, and dollar amount columns into it's own sheet that gets uploaded to the custodian for purchasing.
Is power query the right way to approach this?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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 - Manual Entry and Multiple UsersI have a file with details of open purchase orders. PO numbers are in the rows and there are several columns with various PO details. It is linked via PQ to two other files. It is used by multiple buyers and I'd like to dumb it down as much as possible. I need to be able to do two things that I haven't figured out yet: Each buyer is responsible for updating delivery ETAs on their open purchases. These are updated at different times for a each vendor throughout the week. In the shared file that we previously used, buyers had a sheet for each vendor and we used VLOOKUPS to pull data from these sheets. Obviously, that doesn't work with a PQ chart. Is there a solution to this that I'm not thinking of? I need a column where buyers can manually add notes to each cell. submitted by /u/free-range-human [link] [comments]
- Power query and manual table next to itHi, I want to pull data verbatim from a spreadsheet my team uses and use data from it for my own purposes. The main goal for using power query is that the data updates on my spreadsheet. Mainly, if any new entries are added at the bottom. I also have some manual fields that I need to add that correspond with the power query data. I've added another table beside the power query data, and filtering it causes the data on both sides to adjust correctly. I'm mainly concerned that, if the entries are rearranged or sorted on the original sheet, that my tables will not align after a refresh. Also, if a refresh would break my table alignments at any point. Is my fear founded? Is there a way to combine the two features that I need into a single table? submitted by /u/Perspective-Guilty [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]