|
Everett False posted:Is getting PowerQuery to source from relative file locations as difficult as the internet is making it look? I just want to be able to copy a folder containing the file and the subfolder to Wherever and still have it work. Having to dedicate a cell to my worksheet's current file location seems like a bonkers solution versus just using something like ./ but maybe I'm the weird one for thinking so. It's a headache, I haven't found a straightforward way to do it either. I've resorted to making a tab containing a Table with information I want and then making the tab unviewable. Regardless you somehow need to create a Parameter with the file path you want to use Depending on where you're pulling data from you might be able to construct the path from alternate queries.
|
#
¿
Jan 11, 2022 16:17
|
|
|
|
| # ¿ Apr 28, 2024 09:55 |
|
|
Rabbit Hill posted:I have a big spreadsheet which was set up in a clumsy manner, the biggest problem being that it combines two variables in single columns. I want to separate these columns of combined variables into separate columns. Power Query is absolutely the way to go about this, as others have mentioned. If you haven't done it before, it's under Data. You can create a new file and Get From Other Source, then point it at the original file. Another way is to make your data into a table by highlighting and Ctrl + T (or Insert -> Table), then using Data -> Get Data From Table The interface is pretty intuitive even if the process might not be, but you can fiddle with the steps and it will process the entire file how you tell it.
|
|
#
¿
Feb 3, 2022 18:40
|
|
) of shortcut, so I don't have to do it by hand? The actual spreadsheet I'm working with is really big (imagine 100 agencies, 20 crimes each, for 15 years) and ain't nobody got time for that.
|
C-Euro posted:Can you define the values that populate the drop-down list without needing to reference cells elsewhere in the sheet? That appears to be my only way of getting more than one value onto the list. If you want to get super-fancy you can use Tables and INDIRECT so you only have to define the range once, and it'll update automatically if you add anything to the list of acceptable values. You'll still need a range in the spreadsheet for the list though. If you have a Table named Options with a column named Choices with all your list values, you can go to Data Validation on a cell and set it to " =INDIRECT("Options[Choices]") " Modifying the table will modify the pull-down list, so you never need to worry it's grabbing the right range. Nice for adding a quick value to the end, or avoiding extra blanks when deleting unnecessary ones. Plus you can sort the column! With a little more fuckery you can change which table you reference and make dynamically-allocated pull-down lists! Hughmoris posted:Any M-code gurus out there using it daily? If so, your thoughts? I've recently started exploring Power Query inside Power BI, seems like a fun little language. Not daily but I used it a bunch for a custom project, it's a fun way to attack some problems sideways For example Wandering Orange posted:Add the CSV file as a data source (Power Query > From File > From Text/CSV), ignore whatever happens in the first dialog and hit the Edit button to open up the PQ editor. Then on the far right-hand side, click the gear icon to the right of the Source step on this new query and play around with the Line Breaks and Delimiter options. You've kinda already done this so it may not work either. The Transform > Split Column > By Delimiter option may be useful too.
|
|
#
¿
Oct 26, 2022 01:15
|
|
|
Jack the Lad posted:Is there a way to write the name of a named range as text in a cell, then pass the contents of that cell to a formula that parses it as the named range? Using INDIRECT and naming cells will probably do what you want
|
|
#
¿
Nov 8, 2022 01:40
|
|
|
Xenoborg posted:Is ther a way to resolve all of the references in a formula? For budgeting, I have a cell of all my credit cards bills due that is =$F$19+$F$20+$F$21+$F$22. When all statements for a month are in, I paste values, and it becomes a flat -2500, but what I really want to see is for it to bring in all the refs and make it like =-1000-1000-200-300, which would make checking a lot easier when something is wonky. Probably not exactly what you're looking for, but you can use the Evaluate Formula to step through the calculation and see where things break
|
|
#
¿
Oct 6, 2023 16:24
|
|
|
It's not the most sophisticated way, but it's probably about the speed you're operating at based on your description. Use Column Groups to either make the master sheet hide the calculations columns, or insert blank columns in the Input sheet and collapse them until you're ready to copy/paste. There's a button to (un)collapse all which will save you time either way. https://www.excelcampus.com/tips/groups-outlines/
|
|
#
¿
Jan 24, 2024 00:58
|
|
|
disaster pastor posted:I must either be explaining this wrong or completely misunderstanding, sorry. You'll have to use your template sheet, it's going to replace your input sheet. If you're testing things out, first copy your input sheet data to the template sheet manually. Copy from the now filled out template sheet (presumably with columns collapsed) and paste into your master sheet and everything should be in the right spot. If that works, just provide the template sheet to your users as the input sheet. With columns collapsed it should be identical for their purposes. And since you're dragging down (double-clicking the + drag) formulas anyway, any errant data should be overwritten
|
|
#
¿
Jan 24, 2024 04:27
|
|
|
Separate post for a separate postGD_American posted:Similar question. I have to copy info from a program and paste it into an Excel tab every day, to try and keep track of a specific type of transfer we have a quota for. (Our MI sucks) You might want to look into doing a Power Query. You can set things up so that users just have to place an excel file (or even some other format, like .txt, based on how that other program makes its output!) into a specific folder and the Power Query will filter, process, format, and calculate automatically. This video goes over some of that https://youtu.be/Nbhd0B5ldJE?si=TCJtJZdeF22oRvVj
|
|
#
¿
Jan 24, 2024 04:43
|
|
|
disaster pastor posted:Unfortunately, the input sheet is a system-generated CSV, so I can't replace it this way. The Power Query method might be a good idea for you too then. If you find yourself repetitively processing data the exact same way on a frequent basis it's a good method for automating all those steps.
|
|
#
¿
Jan 24, 2024 04:46
|
|
|
|
| # ¿ Apr 28, 2024 09:55 |
|
|
Azran posted:Hey thread, quick question. My mom recently opened up a wholesaler and given we live in an economy with rampant inflation, keeping up to date on supplier prices eats up a lot of her time. I'm very Excel-illiterate so I don't know if this is possible but it's essentially black magic so might as well ask: is it possible to link a value from a website (which appears on the page source) to a cell and have it stay up to date with any changes made to the website? I might be sounding like a broken record at this point but you can do it with Power Query https://youtu.be/j_ONaX0Ettw?si=jKoLtcd7SHEN8jWo VBA may be more efficient and elegant but this requires less effort or expertise.
|
|
#
¿
Jan 25, 2024 00:48
|
|