Summarize simple use of Power Query for impatient experienced user with simple need?
Our take
I'm a longtime Excel user, comfortable in using it for all kinds of text manipulation and basic number crunching, beginning to get a handle on tables and new 365 formulas. I've learned most of what I know from self study and enjoy it, but at the moment I'm a bit overwhelmed with trying to get started with Power Query. It seems like just the tool for what I need to do, but it's also designed for a lot more, and I'm having trouble getting my bearings since even the basic introductions assume that my needs are much more complex than they are and talk about a lot of options that are irrelevant to me.
This is all I need to do:
I have a workbook with a table where I keep track of work on inquiries sent to an online platform from which I can export submissions into Excel. The export contains a few columns I don't need, and lacks a few others that I add in the workbook, so what I've been doing is opening the export file, deleting unneeded columns, adding blank rows to the bottom of the table, copying the data from the export file, and pasting it in beginning with column D.
Seems like just the kind of thing to automate with Power Query. But I don't want to keep the export file anywhere once I've appended its contents to the workbook. The data remains in the online platform if I need it, and storing any subsets would only waste space and cause confusion. I want to export the latest submissions, transform them with Power Query (I've already figured out how to do that), append them to my workbook, disconnect the two files, and delete the export. Sounds simple enough. But I can't seem to find out how to do this very simple thing without having to sit through long explanations of things I don't need to do and am too busy to wrap my head around right now.
Can anybody point me to some simple step-by-step directions that just tell me how to do only what I need to do without bothering with all the other possible scenarios? Or is Power Query overkill for my needs? Is there another alternative?
Thanks.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Did I follow the best practice using Power Query?I recently needed to build tables of full moons and solar eclipses from 1948 through the present. I thought this would be an excellent opportunity to use Power Query for the first time. I found the data online and used Power Query to pull it all into Excel. In the case of the full moons, each year was on a separate web page. So, I ended up with 79 separate queries. After investigating how to do so, I was able to merge the 79 separate queries into one. Once I had the tables with the data I wanted. I hard copied and pasted them and deleted all the queries. Without a trace of the queries left behind, they now appear as if I undertook the tedious task of typing hundreds (maybe more than 1,000) of entries into the individual cells. From what I understand about Power Queries, these are rebuilt each time I open the file. Since the historical data will never change, there is little reason for that. Further, I have no way of knowing whether the websites I used will have the same URLs forever. Presumably, any change would break the query. Later this year, I'll add 2027 fulll moon and solar eclipse data. I can run new Power Queries, append them to the tables I have, hard copy and paste them and then delete the queries. It seems I can do this every year until I am too old and feeble to continue. I would appreciate critiques from experienced Power Query users about the decisions I made after considering the particular circumstances. Would there have been an advantage to keep the Power Queries intact? Did I speed up the calculation of the workbook by deleting the Power Queries? submitted by /u/Taxman1913 [link] [comments]
- Tools for exporting data from PDF to ExcelHi everyone! I started a new job a few weeks ago and a big part of my role involves extracting data from numerous PDFs (e.g., invoice numbers, amounts, total packages, etc.) and entering them into a massive Excel master file. This file acts as a registry and the foundation for other documents. I’m looking for something that saves me from doing 'copy-paste' all day, hundreds of times over. Browsing this group, I noticed some people suggest Power Query for similar tasks, but I’m not familiar with it and would have to learn it from scratch. Does anyone have any tools to recommend, perhaps something more user-friendly than Power Query? submitted by /u/BomboGanoush [link] [comments]
- Power query automation with combining two tabsI want to create either a skeleton workbook folder or a template where people can upload their data and it runs all of the conditions on power query that I want. Also, it pulls definitions from a secondary tab and matches them with terms that are from the query and merge them. I basically just want them to be able to paste their data raw and it comes out the way I format it with the steps I’ve already created in query. I have watched every YouTube. Searched. Everything We write a report every month and I am trying to make it a very user-friendly report for them and minimize the extra information They don’t need and also link definitions to be able to understand. Please help. submitted by /u/HeavyBreadfruit3667 [link] [comments]
- Excel power users: What finally made you take the plunge into Power Query?I discovered Power Query last year when a coworker solved an issue I had with simultaneously updating multiple tables. I don’t know how I’d gone so long without having heard about Power Query, but immediately had to figure out how this sorcery worked, and now I can’t imagine using Excel without it. I’ve been spreading the PQ gospel among my coworkers, all very Excel-savvy users who prepare recurring reports with lots of manual data sorting/formatting - the ideal folks to take full advantage of what Power Query has to offer. And, as no good deed goes unpunished, I’ve been asked to give a presentation on Power Query: the basics on what it is, how it can improve the workflow, examples on how I’m using it, and point them towards some learning resources. So… I’d love to hear from people who were initially on the fence: What made you finally decide to learn Power Query? What kind of work were you doing before implementing Power Query and what does it look like now? How much time and frustration has this saved you? Bonus: Any "I can’t believe I used to did it this way" stories. Double Bonus: Any "I tried Power Query and it was a total waste of my time" stories (because I find it hard to imagine any situation where this might be true). Hoping you guys will share some of your experiences to help others get over that initial learning curve! submitted by /u/KimHoJo [link] [comments]