How To Install Power Query in Excel 2010

※ Download: Power query excel
Now, the 1st challenge is standardizing the data. These formats can easily and much faster than Excel-files be imported by other workbooks then. Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel A Microsoft Query aka MS Query, aka Excel Query is in fact an SQL SELECT Statement. If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts.

If they are on Excel 2016 then PQ is built-in and they will not need to install anything. If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts. Ken and Miguel Escobar have also published a great book on Power Query titled,.

How To Install Power Query in Excel 2010 - Â Plus, employers of the future will definitely be looking for employees with Power Query skills.

Bottom line: Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports, and so much more. Skill level: Beginner In this tutorial I provide an introductory explanation of Power Query.  You will learn why this is my new favorite Excel tool for working with data, and how it can help automate processes and save you time! The Power Query Data Machine I was watching a TV show on how things are made, and they were explaining how a depositor machine worked in a pastry factory. Once the dials are set, the process can be repeated over-and-over again to make perfect pastries every time. Power Query is like a machine because once you have your query setup, the process can be repeated with the click of a button every time your data changes. If you have used macros to transform your data, you can think of this as a much easier alternative to VBA that does NOT require coding. Common Data Tasks Made Easy Do you work with data that has been exported from a system of record?  This could be a general ledger, accounting, ERP, CRM, Salesforce. If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts.  If so, then they probably also sound boring, repetitive, and time consuming. Fortunately, Power Query has buttons that automate all these tasks! Overview of the Power Query Add-in Menus Once installed, the Power Query tab will be visible in the Excel Ribbon. Click to Enlarge You use the buttons in the Power Query tab to get your source data.  Again, your data could be stored in Excel files, csv files, Access, SQL server database, SharePoint, Salesforce. Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data. The buttons in the Power Query Editor Window allow you to transform your data. Think about some of those tasks you do repeatedly as you browse the buttons in the image above.  Each time you press a button your actions steps are recorded, and you can quickly re-apply the steps when you receive new data. You can also modify existing queries and refresh your output tables with the changes or updated data. Here are a few examples of what Power Query can do with your data.  This is a technique used to get your data ready for the source of a pivot table.  This is also referred to as normalizing your data to get it in a tabular format. The data might start out looking something like the following. And you want the end result to look like this. Power Query can do this with the click of a few buttons, and prepare your data for use in a pivot table. Here is an article and video on exactly. Checkout my article on if you are unfamiliar with why your data needs to look like this for a pivot table. Append Combine Tables with Power Query The Append feature of Power Query allows you to combine multiple tables stack them vertically to create one large table.  Throw all those files in the Power Query machine, and it will spit out one nice table that you can then use to create pivot tables and charts. If the data in those reports also needs to be transformed remove rows, split columns, unpivot, etc. Once it is setup, all you have to do is hit the refresh button every month when a new file is added to the folder and the rows will be added to your output table. How awesome is that!  Your product group information is located in another table on a different sheet or workbook. Using is great, but it can often mean adding thousands of formulas to your workbook.  Which increases the file size and calculation time. Power Query makes it very fast and easy to merge two tables together with just a few clicks.  It basically uses SQL joins, so you can even do more advanced merges like inner, outer, left, right, full, and anti joins. Create Custom Functions Power Query was designed so that you do NOT have to know how to code to use it.  It is very easy to use because you can just click buttons and apply filters like you normally would in Excel. However, Power Query can be programmed to create custom functions.  This gives you seemingly unlimited potential to transform your data in just about any way possible. It is based on the M language, and most of the functions are very similar to writing a formula in Excel.  This also makes it more user friendly and easier to learn the code. This new language and set of functions means there is a lot to learn, but I consider that the fun and challenging part.  Plus, employers of the future will definitely be looking for employees with Power Query skills.  It will save you a lot of time if you are preparing the same data every day, week, or month. It also does a pretty good job of handling errors.  If the structure of your source data changes, Power Query will tell you what step it broke at and allow you to fix it. You can use Power Query to get your data ready for use in.  This is a critical step in the process of summarizing and analyzing data.  It will save you a ton of time when transforming your data. Power Query is just one piece of the suite of Power BI Business Intelligence products from Microsoft. If we go back to the analogy of baking cookies in a factory, you can think of Power Query as the first step in the assembly line.  Once the cookies are formed, we then need to bake them Pivot Tables, PowerPivot and then package them for presentation Power View, Power Map, Charts, Dashboards, etc. You can think of Excel as the factory building that houses all these tools.  The exciting part is that there is a very bright future for Excel!  All these advances in technology will help us make sense of our data in new ways, save us time, and impress our bosses. The other nice part is that it is a FREE add-in from Microsoft for Excel 2010 and 2013. I have a dedicated page that will help you determine if you have the right version of Excel to get Power Query.  It also provides complete installation instructions and the download link. To give you an idea of the importance of this tool, Power Query will no longer be an add-in in Excel 2016.  Instead, it will just be part of the Data tab on the Excel Ribbon. Additional Resources This article has provided an overview of the basics of Power Query that should help you understand some of the major features.  Power Query has a ton of features and there is definitely a lot to learn. I will be sharing more how-to articles and videos in the coming weeks.  Here are a few resources that will help you get started. LIVE Training Workshop on Power Query My friends and fellow Microsoft MVPs, Ken Puls and Miguel Escobar, are hosting there last Power Query Training Course of the year. This is a LIVE, hands on training workshop that will get you up and running with Power Query.  The workshop is conducted online, so you can take it in the comfort of your home or office. Right now you can save 15% on the workshop enrollment fee by using the discount code: LAST2016 This is an incredible opportunity to get instruction from two of the top experts in the field. The workshop starts on November 16th, and all sessions will be recorded and included with your registration. Spots are limited for this live workshop, so get registered before the class fills up. Ken and Miguel Escobar have also published a great book on Power Query titled,.  Here is my affiliate link to Amazon. What Do You Think? Are you using Power Query yet?  If so, below letting me know how you use it. If not, do you think it would be useful for you?  Are you doing any of the tasks I mentioned manually right now? Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. I know that writing articles is boring and time consuming. But did you know that there is a tool that allows you to create new articles using existing content from article directories or other blogs from your niche? And it does it very well. The new articles are unique and pass the copyscape test. That gets the vehicle id and names and put then into sheet 1 of a workbook. I want to step through the vehicle ids 688 of them and use the selected id in a new query, in the api and get the details of the vehicle into sheet 2. I have used power query and there are a few steps recorded to clean up the info. I can do this 1 vehicle at a time. I want to rather automate the process 688 times. Ant way to do this in vba? Jayadev - October 4, 2017 I need a clarification on Filtered rows. I have applied filtered row in one of the query. Does the powerquery pull all the data from the specified table and then filter it or it pulls only filtered rows when I refresh. Thanks in advance, request some expert to guide me. Terry - August 1, 2017 Following the guidance from your 3 part video guide for creating dashboards from pivot tables and charts, a few months ago I set up a dashboard for data extracted from a SharePoint file for transactions processed on the SharePoint from Jan-Jun 2017. With the use of slicers Management are able to obtain returns for unit and personal performance levels, turnaround times, input, demand and output. I would export the data from SharePoint and save it on a worksheet in my workbook. After exporting the SharePoint data to excel, with the use of various formulas in individual cells, I was able to create my source data based on the exported data. Once a week I would update the source worksheet by exporting the fresh data again. I copied the cell formulas on my source data worksheet to create a range sufficient enough to include the additional data from each update. All was working well until a new SharePoint file was created for Jul-Dec which meant that I now have 2 sources of data for my source data worksheet. Do you want to replace it. As I say, there must be an easier way to do this which is why I am seeking any positive guidance from yourself. Would Power Query be the answer? I work for a government agency and Power Query is not included in our MS Excel 2013 but if it would be the answer then I could submit a business case to have it downloaded. I love your tutorials and have learnt a lot more from them than I have from the advance Excel courses that I have attended through work so any advice would be appreciated, Jon. Many thanks, Terry Arun Kumar - July 23, 2017 Dear Jon, I know something about power query this is very good tool to work on Excel and improve our productivity, but i have never work in power query yet. Request you to please advise how i can get start this tool form begging. Waring for your revert. Thanks Arun Kumar From IND Dennis - June 30, 2017 Dear Jon, I am using Power Query for almost a year now and the tool helps me a lot with processing data. I have one question: I have an Excel file with data regarding sales from different countries. These need to be split into different sheets. For example I want to have a sheet for The Netherlands, United Kingdom, Italy, Spain and France. Do you have a quick solution to get these in different worksheets in one Excel workbook? There is a column where the country is mentioned, so I am able to filter the data. Thank you very much. I am curious as to why this is. If Excel can reach the web page I need to query, why as an Add-on is Power Query having this issue? Can anyone please provide a suggestion of what I can do to avoid this error? Is there any documentation for connecting to the web besides those I have found that make it look so simple and assume no issues? Any suggestions will be much appreciated. However I have a really strange scenario going on with Power Query. I added sample data. I bought 2 of 2 different items. I simply entered a standard math column asking it to multiply. Any Suggestion or direction? Thanks a bazillion… Kevin D in Las Vegas Georg - January 26, 2017 Hi Jon This Addin sounds very interessting. Currently I am searching for an option to combine two Sharepoint-Lists, which I implemented as tables in Excel, into one to use it as a database for a Pivot. Can you tell me, if everyone who uses that file afterwards also needs this Addon installed like if using PowerPivot for example? Regards Georg Georg - February 5, 2017 Hi Jon Thank for the info. Meanwhile I tested it and came to that conclusion, too. Now I am waiting for our IT-Department to handle my ticket requesting the installaton. This Addon could also end my longyear search for a stable functionality to link a standard pivot with two Excel-tables as sources. Page function requires Active Scripting to be enabled in the Internet Explorer options. Why is this and what can I do to remedy the problem? Is there any way that we can apply this filter before fetching the data , i mean add this filter variable in actual query? Jeff Green - August 23, 2016 I have a table in Excel can be in a data model if need be. This table called ACE can have 30,000 unique rows. I want to compare this data to what is in our ODBC table, where that table has millions of rows. I want to import the records from the ODBC tables doing a LEFT JOIN, so to speak, on the ACE table in my data model, Can that be done? Thanks Terry - July 24, 2016 Hi Jon, Thanks for the article. I am an old had at Excel but new to the Power Query and PowerPivot features. I am still trying to work out he differences and when it is best to use one over another. Perhaps there is a post in that for you. My question is that I often get a similar error. I try to link two tables and instead of getting the relevant data to come through to each line I get a sum on every line. For example, say I have a table with 1000 rows of sales data for two products, Product A and Product B, showing quantity sold. I want to pick up the price on each line, multiply it by the quantity and get sales value. You will want to use the Merge feature to bring the tables together. Then expand out the columns. You can then add a custom column to do the math. What steps are you taking in Power Query right now to get the total price? Cristina - July 19, 2016 Hi Jon, This is a terrific post that I forward to my colleagues as an intro when they start working with Power Query. I started having some problems since the update Excel 2016. All the queries I use are connected to SF Objects and generally merge 2 objects. Error: invalid query locator. Do you think changing the location of the file or duplicating them might be a source for the errors? Cristina Quentin - May 24, 2016 Hi, I have series of workbooks I import data from via PowerQuery. The source files only have the Date field completed every 4 rows. There are 3 site specific entries for each date. I have an image of the current output if required. I want to be able to replace each null row in the date column with the value of the row above so that dates appear in every row. Any help gratefully received. Regards, Q - May 29, 2016 Hi Quentin, Great question! There is a Fill Down function in Power Query that will fill the null rows with the value above. To use it: 1. Right-click the Date field column in the Power Query Editor. I just wrote a separate. I also included an animated screencast there so you can see it in action. I hope that helps. Vicky - March 8, 2016 Can dashboards created with Power Query and Power Pivot be shared with users that do not have these add-ins installed on their computers without losing the connection to the analysis server, or do they have to install the add-ins in order to get the full functionality of the shared dashboard? However, if the pivot table is built with PowerPivot and there are slicers, then they will need the add-in to interact with the pivot tables. For Power Query, the user will not need the add-in if they are just interacting with a regular pivot table. They will only need the add-in if they are going to refresh the query data connection to the server. I hope that helps. Let me know if you have any other questions. Another option is PowerBi. This tool basically combines PowerPivot and PowerQuery, and allows the users to view and interact with your dashboards online at PowerBi. It is completely free to use. You can even Excel files to it and objects from the Excel worksheet like pivot tables and charts. I did an a while back, but it has been improved a lot since then. You can also pull data from the Analysis Server. I hope that helps. Yogi - March 1, 2016 Hi Jon, Is it possible to compare two files with particular Column data and then delete from one file let me explain more I have two different file one is EXCEl or PowerQuery file and One is CSV file both has one column common I want to compare both with that common column and delete whole rows from CSV file which has already data in EXCEL power query File I hope you would get my Qeury - March 1, 2016 Hi Yogi, Yes, it is possible. You will probably need to use a macro if you want to automate the process. You could use Power Query to do the comparison, then load a table with the results, then save as a new CSV file. If you want to delete data from the original CSV file then you will need VBA to automate that process. Here is an article on how to use. I hope that helps. I have a question: In power query: I have applied some say 5 steps to 1st workbook file. I load 2nd file, and i want to apply those 5 steps to this file also. My source files are big which are generated weekly. So, if I go on updating those weekly data on a single file my 1st file , then my PC will not be able to handle that 1 big ever-increasing file. So, instead of keeping 1 source file, I want to keep my source files separately, but use the same queries steps to all and go on updating the output file. Any suggestions on how can I do this? Yogiraj - February 18, 2016 Hi Jon, New to PowerQuery.. Actually i have daily one daily process In that process I have to prepare file properly Currently I am using Libreoffice Macro for that Is it possible that I click on one file then Input File open and then It will make proper file and then export as csv I have 50 different files which I have to make it proper daily Input file could be CSV, Excel, Textfile I am able to process it by libreoffice macro Is it possible in Powerquery Thnks Oliver - February 3, 2016 Hi there — have started working with Power Query recently and find it very powerful. If the users are on Excel 2010 or 2013 then they will need to install Power Query on their computer. If they are on Excel 2016 then PQ is built-in and they will not need to install anything. Therefore, they will be able to do the refresh with no problem in Excel 2016. Let me know if you have any questions. Anindya Chandra - December 16, 2015 Hi Jon, Was going through your blog, I am hoping you can help me with my query. I am working on a project where I need to match two data sets from different sources. Now, the 1st challenge is standardizing the data. So, can standardizing be done with Power Query. Ex: like removal of special characters from the column, changing some words to standard format Limited to LTD and number of cleansing process to be carried out. So, can all these steps can be done through power query. Please let me know your inputs on this. Rgds, Anindya - December 18, 2015 Hi Anindya, Great questions! Yes, it sounds like your data cleansing process can all be done in Power Query. Power Query has a Replace Values feature that will allow you to find and replace words. You will also be able to remove special characters with the same feature. The nice part about Power Query is that once you have this query setup, you can just refresh it and all the same steps will be applied to your data. I hope that helps. Thanks and have a good one! Anindya - December 30, 2015 Hi Jon thanks for your input. I tried doing it, but I have to do it for each values I change. Now, this is near impossible, I have around 3000 values to be replaced and all are different. Ex: in a column if limited is there I have to change it to ltd, if company is there then Co, if holdings is there then HLDG. Likewise there are like 3000 replace value needs to done. So, each time I have to click replace value button to replace the particular step. Can this be done at one shot? Can you please help me with this problem? You could do it with either formulas or VBA. You will probably want to create a table that contains two columns with the search value and replace values. You could then use a lookup formulas and the SUBSTITUTE function to find and replace the values. Alternatively you could use VBA to loop through the table and find and replace values in your data set. I hope that helps. Chris L - October 26, 2015 Hello Jon, thanks for your efforts here. Some advice if you have the time please? I have 2 tables where column A in both have numbers 90% common to each table although the order may be different, the other 10% are either unique or blank in each table. So, each table has rows that may? Massaging the tables to be of the same column and row number seems to have helped importing but the resulting table has blank cells for columns B-Z from the second table. Any hints on how to proceed Jon? Diana - September 1, 2015 First time using Power Query and you made it so much easier! Power Query is a pretty awesome tool. Great question about the Data Model. Loading the query to the Data Model will allow you to use the data in pivot tables and Power Pivot pivot tables without having to load the data into a spreadsheet in the workbook. If you have a large data set then this can reduce the file size. The Data Model is only available in Excel 2013 and beyond, so if you have users on 2010 still they will not be limited on what they can do with your workbook. Please let me know if you have any other questions. The query you setup points directly to the CSV file location. You will see this in the Source step of the query. To reuse the query you will need to replace the CSV file with the new one everyday. Then go to Power Query and refresh the query. Just hit the refresh button in the query pain, or right-click the output table and press Refresh. This will re-run the query on the new CSV file and output the data in your table. If you want to add new data to an existing Table, then you can use the From Folder feature to get data in Power Query. This will bring in all the CSV files from one folder. Then you can append the tables together to make one big table. I hope that helps. Let me know if you still have questions. Zahid Hussain - January 15, 2016 Dear Jon Acampora, First of all thanks for valuable material and information. I am going to an offline application on MS excel using Power Query and Power View. You can do this with custom VBA functions. Here is an article that explains how to do it using the Google Maps API and a VBA function macro.
You might think there is a lot of duplication between these two tabs. Let me know if you have any questions. While there is a lot to learn about Power Power query excel, it is worth putting in the time to learn. Â Once the cookies are formed, we then need to bake them Pivot Tables, PowerPivot and then package them for presentation Power View, Power Map, Charts, Dashboards, etc. Then you need to summarize the sales by salesperson and calculate the commission to pay out. I have 2 tables where column A in both have numbers 90% common to each table although the order may be different, the other 10% are either unique or blank in each table. Thanks Matt, it is a very useful technique. If you want to add new data to an existing Table, then you can use the From Folder feature to get data in Power Query. Most likely you will be using 32-bit. You can read the entire power query excel, or jump to its section.