•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Excel Power Query prep and best practice & Power BI
Our take
Are you finding yourself tangled in repetitive Power Query tasks, unsure how to streamline your data preparation? You’re not alone. Many users struggle with the complexities of transforming data effectively, often leading to inefficient workflows. By adopting best practices in Power Query and considering integration with Power BI, you can elevate your data management process from a chaotic “Frankenstein’s Monster” to a sleek, efficient system.
Hi all,
I have been an intermittent power query user for the last few years but now I have a job where it is much more valuable and needed. I would not say I am a beginner but I'm certainly no expert either.
What I find myself doing is having to make the same power query lookup multiple times, mainly due to not being sure what I'm going to do, muddling my way through and creating a bit of a "Frankenstein's Monster" in the applied steps. The end result works, but I know its a bit of a mish-mash and could be more efficient and cleaner.
So, to those of you who are experts (or better prepared than I), do you take time to work out what you are going to do before you start, do you list the columns you will keep/discard and note what lookups and transformations you plan to do? Are there any good tips and tricks (or even videos) on best practice, preparation etc.
I also create a lot of governance checks on the data in the columns which makes my file really long and a bit hard to manage. I have considered doing that on a separate page, but then I don't think I can filter back to the main data to see the errors I catch - any advice for that?
Also, is it worth connecting into Power BI for the visuals? I am fine doing pivots and the usual graphs/charts in Excel, but I've heard Power BI is "better". Is it worth investing my time to learn to connect my output data to Power BI?
I will be using power query a lot going forward, and some major projects are reliant on me getting data cleaned, transformed and ready for go-live so I am looking to adopt a better methodology going forward.
Many thanks to any of you who reply.
[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]
- 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]
- 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]
- Summarize simple use of Power Query for impatient experienced user with simple need?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. submitted by /u/fastauntie [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#real-time data collaboration#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#intelligent data visualization#Excel compatibility#rows.com#Power Query#Excel#Power BI#best practices#data transformation#data cleaning#preparation#methodology#governance checks#lookups#efficiency