Did I follow the best practice using Power Query?
Our take
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?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Power Query + Power Pivot + DAX = fast and powerfulI inherited a monthly Update that is mostly the same data updated and then some analysis. I paste the raw data from 9 different places, manually aligning them as a single table. There are a load of pivots that have calculated fields and items and updating them take a morning and most of that was just waiting for the calculated items in the pivots to refresh. While I was waiting for that to refresh, I decided to see if I could recreate the same pivots in a new sheet, using powerquery. In the time it took for my original sheet to refresh, I loaded the data into powerquery, aligned the data, loaded everything into the model, created custom lists for the columns to look up off to allow for differences in the names between data sources, created extra lookups to improve the filtering, created the relationships, created DAX functions to replace the in-pivot calculated items and fields and recreated the tables and charts I use every month. The original refresh finshed about 5 minutes after I did all that. I did a test refresh and what took a morning now takes about 3 minutes on my new sheet. I'm a fairly recent convert to PQE, but this little project is the first time I've really dug into Power Pivot. It could be a game changer for a lot of my work. There's stuff I was doing in PQE that is a lot easier to do in PowerPivot. Being able to link lookup tables, data tables and facts together is really fun. submitted by /u/Thisoneissfwihope [link] [comments]
- PowerQuery and add manual dataHi everyone, I have a Power Query in Excel that outputs a table with [title] [date]. I need to manually add the Sprint number in an extra column [sprint] to specific combinations, aka "I will work on this this month." The problem is every time the query refreshes, any manually entered data gets lost or misaligned. - New rows come with the needed values. - Row order changes. Because this table is used by many people, I want them only to add the sprint number, nothing else, no copying data or anything. I would like to know more about your experiences when data needs to be written infrequently but many times. I am open to know more for powerbi options direct dashboard too. submitted by /u/No_Solid2349 [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]