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

Power Query Overview Data Machine

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.

Power Query Cookie Machine

The basics of a depositor machine are:

  1. You add ingredients to it.
  2. Change some settings.
  3. And it magically creates pastries (cookies, donuts, biscuits) that are ready for baking.

Once the dials are set, the process can be repeated over-and-over again to make perfect pastries every time.  Getting hungry…? 🙂

Power Query works in a very similar way!

Power Query Process Overview Steps - Transform Data

  1. You add your data sources (Excel tables, CSV files, database tables, webpages, etc.)
  2. Press buttons in the Power Query window to transform your data.
  3. Output that data to your worksheet or data model (PowerPivot) that is ready for pivot tables or reporting.

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,, or any reporting system that contains data.

If so, you probably spend a lot of time transforming or re-shaping your data to create additional reports, pivot tables, or charts.

Unpivot Exported Data with Power Query in Excel

These data transformations could include tasks like:

  • Remove columns, rows, blanks
  • Convert data types – text, numbers, dates
  • Split or merge columns
  • Sort & filter columns
  • Add calculated columns
  • Aggregate or summarize data
  • Find & replace text
  • Unpivot data to use for pivot tables

Do any of these tasks sound familiar?  If so, then they probably also sound boring, repetitive, and time consuming. 🙂  Believe me, I’ve spent the better part of my career doing these tasks and trying to figure out faster ways to get them done.

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.

Power Query Ribbon for Excel v2.16.3785.242
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,, Dynamics CRM, Facebook, Wikipedia, websites, and more.

Once you have specified where your data is coming from, you then use the Power Query Editor window to make transformations to the data.

Power Query Editor Window Home Tab Transform Data with Buttons

The buttons in the Power Query Editor Window allow you to transform your data.

Power Query Editor Window Home and Transform Tab Buttons

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.

After completing your steps, you can output the data to a Table in your Excel workbook by clicking the Close & Load button.

Close and Load Button in Power Query Editor Window

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.

Unpivot Data for Pivot Tables

My favorite feature of Power Query is it’s ability to Unpivot 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.

Exported Report Data in ExcelFile

And you want the end result to look like this.

Unpivot Data with Power Query for use in Pivot Table

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 How to Unpivot Your Data with Power Query.

Checkout my article on how to structure your source data for a pivot table 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.  It can do this with multiple tables in one file, or it can pull in data from a bunch of different files/sources.

Let’s say you have a folder that contains CSV or Excel files with report data for each month.  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.

Append Combine Multiple Excel Files to One Table with Power Query

If the data in those reports also needs to be transformed (remove rows, split columns, unpivot, etc.), then Power Query can handle that in the same process.

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! 🙂

Merge Tables – A VLOOKUP Alternative

Power Query has the ability to merge or join tables.  This can be used as an alternative to VLOOKUP or INDEX/MATCH formulas.

Merge Join Tables with Power Query in Excel Alternative to VLOOKUP

Let’s say you have this data table of sales records, and you are using a VLOOKUP to bring in information about the product based on the name of the product sold.  Your product group information is located in another table on a different sheet or workbook.

Using VLOOKUP formulas 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.

Power Query Advanced Editor M Code Language

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.

Power Query Records Your Steps & Automates Processes

Power Query not only makes all these tasks easier, but it also records your steps so you don’t have to do them over-and-over again.  It will save you a lot of time if you are preparing the same data every day, week, or month.

Power Query Records Applied Steps and Automates Processes

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.  This makes maintenance easy and you don’t have to completely redo your process when something changes.

You can use Power Query to get your data ready for use in pivot tables, charts, and dashboard reports.  This is a critical step in the process of summarizing and analyzing data.

The Power Query Machine & Power BI

Well, it can’t exactly make cookies, but Power Query is a pretty awesome tool!  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.)

Excel Power BI Solution Overview - Baking Cookies

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. 🙂

How Do I Get Power Query?

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.

Complete Guide to Installing Power Query

Complete Guide to Installing Power Query

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.

How to Unpivot Your Data with Power Query + Video Tutorial

Online Training Program

Our new program, Elevate Excel, is a comprehensive (beginner to advanced) training program that covers all areas of Excel, including Power Query.

Elevate Excel Logo on Monitors 600


Click here to learn more about Elevate Excel

What Do You Think?

Are you using Power Query yet?  If so, please leave a comment 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?

I will be writing more how-to articles and videos on Power Query in the future, so I’m interested to know what you want to learn.



Your email address will not be published. Required fields are marked *

  • I have a nagging problem in the power query editor and connecting tables. In the cleaning process in power query for What will become a dimension table I remove duplicates in the column that will link to my fact table, or so I think. When I go to create the connection in the data model I get the error that there are duplicates. This happens frequently. The step to remove duplicates in the selected column does not remove the duplicates. I load and refresh everything but I have the same amount of rows after the remove duplicate step is applied, thus no duplicates are removed. I must be doing something wrong and/or I don’t understand the remove duplicate behavior. Further, I don’t know how to investigate the problem in the power query editor when my data contains more rows than can be previewed. Any wisdom would be greatly appreciated.

  • I have a workbook that contains a data source built on a separate worksheet and it contains many pivot tables and a dashboard. We recently made some major changes to the source worksheet to include a new campus for our church. After updating the power query and creating several different queries that are merged I need to change the source for my pivot tables and dashboard. When I attempt to change the source I get a message that “The data source of a PivotTable connected to filter controls that are also connected to other PivotTables cannot be changed. To change the data source, first disconnect the filter controls from this PivotTable or from the other PivotTables”
    I removed the slicers and don’t see any filters. Where do I need to look?
    Thanks for any help you can provide.

  • Scenario:
    I’m using power query to import a data set to an analysis workbook from another excel source data workbook. I import the data and save it as a connection only. From the connection I then run several other power queries that load data to a worksheet. I want to send the analysis file to users, but not the source data file.

    When the analysis file can no longer access the source data file, the source data still shows in the connection preview, but refreshing the other queries that load data to the worksheet do not return data. I initially thought the raw data was saved somewhere in the analysis file (hence how the preview can display data), but I now suppose it does not, but instead, that it has to refresh the connection every time a sub query pulls from it.

    Is there a way of importing the raw data to the query so that it stays in the connection and does not need refreshing when a sub query runs? I’m hoping I do not have to load the raw data to a worksheet to achieve this as it will increase file size (maintaining a smaller file is one of my goals)

  • Hi Jon, late to this post by a few years, but I’m wondering what/if any advantages there are with Power Query to regular old MS Query in my scenario. Our ERP is on an SQL database, and I really only have one source for data (no Facebook analytics coming in!). We’ve been using MS Query up to now. Our versions of Excel don’t have Power Pivot, so I can’t utilize that either (and again- what’s wrong with regular old pivot tables and charts?). I’m just exploring whether upgrading will have a payoff. Thanks!

  • Hi. I see that you don’t update your blog too often. 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.
    You should try miftolo’s tools

  • I have successfully got info from an api using query. 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.
    the api is
    Now 3601 is the number that must be changed to the one im stepping through in sheet 1.
    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?

  • Hi Jon,

    How to connect my FTP folders and get data file from that ?
    I have tried with FROM WEB option but its getting error
    Can you please explain process to connect FTP in Power Query

    Same as is it possible to connect SFTP ?

  • 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. I want to minimize the time for data extraction, that’s my objective.

  • 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. They loved it!
    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. Once my source data was updated I would select a pivot table and use ‘refresh all’ to update the charts on the dashboards.
    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. Following the process I had been using, I now have 2 worksheets in my workbook, one which is a copy of exported data for Jan-Jun and one for Jul –Dec, and my source data has been formulated to include both. I’m sure there must be an easier way to do this. Although all the required data is on the source data worksheet, the problem I am now facing is that when I select ‘refresh all’ I get the following pop-up messages for every worksheet that has my pivot tables:
    ‘A PivotTable report cannot overlap another PivotTable repot’ & ‘There is already data in ‘(worksheet with tables)’.Do you want to replace it.’ which ultimately removes most of my filters and deletes the respective slicers from the dashboard.
    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,

  • 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.
    it would be great help if you can provide some link of Videos where i can get learn quickly form starting.

    if something else from your side to please suggest i want to be expert in Power Query because i’m also want to help others in theirs works as well.

    Waring for your revert.

    Arun Kumar
    From IND

  • I have installed power query for excel 2010,but most of the easy steps are not possible with that version. How can upgraded to new power query version.

    I.e combining multipule excel files process is difficult than new version as shown in the tutorials.

    pl. help

  • 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. This website is really useful by the way 🙂

    • You can make a pivot of your data and then use PivotTableTools>Options>ShowReportFilterPages.
      To use this, you need to put Country in the filter section of the pivot table.

  • Good Morning Jon,

    I am trying to prepare one file which need to formatted with some HTML tags and I don’t require that so I was trying to remove those tags but I couldn’t find a way here is requirement like “” to “”
    For example I want to make it only “” to “”
    Of course I can do it with Replace but there are so many other tags needs to be removed
    like , and so on..
    Is it possible ?

  • Good Morning Jon,

    I am trying to prepare one file which need to formatted with some HTML tags and I don’t require that so I was trying to remove those tags but I couldn’t find a way here is requirement like
    For example I want to make it only
    Of course I can do it with Replace but there are so many other tags needs to be removed
    like , and so on..
    Is it possible ?

  • The request was aborted: Could not create SSL/TLS secure channel.

    ‎I am trying to connect to a state website to import data from the web using Power Query. I can connect to it with a browser and I can access the site using Excel>Data>From Web. Because the Excel option is limited I decided to try the Power Query add-on but get the message

    “The request was aborted: Could not create SSL/TLS secure channel.”

    when I try to connect. 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. Thank you!!

    • Hi JTR,
      I’m not sure about this error. I would try posting or searching it in the Microsoft Answers forums. There are Microsoft employees there that will help resolve your issue. Thanks!

  • Hello Jon,
    I like your website and I want to say that it has been very beneficial to me.
    However I have a really strange scenario going on with Power Query.
    I added a Standard math column to multiply “Price to Sell” by the Qty.
    I added sample data. I bought 2 of 2 different items.
    Item 1 has a price of $9.39 to sell and Item 2 has a price of $16.12 to sell.
    For Item 1 the query said that $9.39 X 2 is $9.39 and it then returned a product of $103.1928 for the second item.
    I don’t understand why this is happening. I simply entered a standard math column asking it to multiply.
    Any Suggestion or direction?
    Thanks a bazillion…
    Kevin D in Las Vegas

    • Hi Kevin,
      I’m not sure why that would be happening. Sounds strange. You might want to try posting it on the Microsoft Answers forum.

      Thanks again for your support. I’m happy to hear you are enjoying the site.

  • 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?


    • Hi Georg,
      They will only need the add-in if they are going to refresh or modify the queries. If they are just going to view the file and modify it’s formulas, pivot tables, slicers, then they won’t necessarily need the Power Query add-in installed.

      • 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.


  • Hello Jon,

    I got stuck,
    My powerquery has 9 excel files and I am doing some operations on that, It was running successfully but since friday when I try to run I’ts getting error like

    [DataSource.Error]: The Web.Page function requires Active Scripting to be enabled in the Internet Explorer options. Please see …….mswebsite…. for details on how to enable Active Scripting.

    I have checked about Active script on IE but It’s already active

    However Its getting same error, I couldn’t process further
    Can you please help on this error”


  • Hi Jon,

    I am encountering a problem where data that I have grouped by and summed does not exactly correspond to the value I get when using a Pivot table with the exact same grouping and summing parameters.
    Why is this and what can I do to remedy the problem?

    Thanks for a great article 🙂

    Marius Gambold

  • Hi,

    i have multiple queries i am going to run it on oracle database and fetch data. i have predefined filters on Name and id .
    i added this name and ID In the excel table.
    i can add this dynamic filter on the results of power query in advance editor,
    but issue here is- it is fetching all the data to excel and then applying this filters.

    1.Is there any way that we can apply this filter before fetching the data , i mean add this filter variable in actual query?
    ( i tried its throwing error as reference to other steps.) there any way that i can build my query in excel cell with all my dynamic filters and use this query in power query?


  • 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?
    I know how to create a filter, and even use Design mode in PowerPivot to create a query, but i cant figure out how to do a “LEFT JOIN” to only pull in the the rows in the ACE table.


    • Hi Jeff,

      In Power Query you can use the Merge Queries feature to do a Join. The Merge window allows you to choose which join type you want to use. I hope that helps get you started.


  • 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. On the second table I have prices for two products, A is $10 and B is $15. I want to pick up the price on each line, multiply it by the quantity and get sales value. Instead of picking up the individual prices ($10 and $15) I get the total price ($25) for each line.
    Any suggestions?

    • Hi Terry,
      Thanks for the suggestion. 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. I’m not sure I fully understand your question though. What steps are you taking in Power Query right now to get the total price?

  • 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. In some cases I get errors as
    a) “DataSource.Error: invalid query locator. Details: List”

    When I scale a model existing for “Country A” with a query connected to SF, I usually duplicate it in another Folder with name “Country B” and change the filter in Power Query Editor that defines which country we download the data for.
    Do you think changing the location of the file or duplicating them might be a source for the errors? In some files it’s still working, specially for my colleagues, that is the tricky thing.

    If that wouldn’t be the error, is there other sources that could cause this?

    Congrats again for the post & many thanks!


  • 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

  • Hi Jon,

    Please I need your help. I would like one book to learn hot to use Power Query. For my job will be so important. Maybe you know a special author (I would like to buy a book about this subject)

    Thanks a lot for your reply to my email

    Warmest regrads,

    Rodney Gonzalez

  • 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?

    • Hi Vicky,
      Great question. If the user is just going to view the dashboards then they won’t need the add-ins. 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 This tool basically combines PowerPivot and PowerQuery, and allows the users to view and interact with your dashboards online at 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 overview video on 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.


  • 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

    • 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.

      You could also open the CSV file in Excel, add a column with a lookup formula like COUNTIF to check if the value exists, filter and delete rows that don’t match, delete the column with the lookup formula, and save the CSV. Here is an article on how to use COUNTIF instead of VLOOKUP.

      I hope that helps. Thanks!

  • Hi Jon,
    This is good page & tremendous work. 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.
    Though both files are similar, i don’t want to update my 1st file from 2nd file.
    I want to keep the source files (say 1st/2nd/3rd files…) separately, load each of these files separately, and use applied steps (from query editor) to these files.

    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?

  • 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

  • Hi there – have started working with Power Query recently and find it very powerful. However I wondered whether you know if there is a way you can run a query created from Power Query on a PC that doesn’t have power query installed?

    Eg I have a shared spreadsheet in which I have created a query using Power Query, and I’d like other people to be able to refresh the query without having Power Query installed.

    Any ideas?


    • Hi Oliver,
      Great question! 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. Thanks!

  • 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.


    • 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. It’s similar to a macro, but you don’t have to do any coding.

      I hope that helps. Thanks and have a good one!

      • 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?

        • Yes, I see how that could take along time with that many different replacement values. 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.

  • 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 (?) key to the other row. All other columns are totally different in content & number.

    I’d like to add each matching row of table 2 to the end of the matching row of table 1 by column A number but am stumped by the Merge function. 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?



  • First time using Power Query and you made it so much easier! My data is in different tabs and I used the “Append” button to combine all tables into one single table. During the process, I am selecting “Only Create Connection”. I noticed there is a check box for “Add this Data to the Data Model” Wondering if you can clarify what that would do?

    • Thanks Diana! 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.


  • Yes it is very good but I cannot work out how to save and reuse the queries created.
    I get a csv file every day which I manipulate the way I want it.
    I have gone through the power query steps, go want I want, but cannot see where I reuse the query.

    • Hi Stephen,
      Great question! 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.

      Let’s say you saved the CSV file at the following location. C:\Documents\MyData.csv

      When you get the file tomorrow, you will need to save your file to the same location, replacing the existing file.

      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. Thanks!

  • Love the analogy, Jon.

    I too am loving Power Query at the moment.

    It’s just a shame you’ve got to have a specific version of Excel to get it. I think everyone with Excel should be able to have it.


    • Thanks Mynda! I completely agree. I wish Power Query was available for everyone. I think it’s worth the upgrade if you have a need to automate any data related processes. But it would still be nice if everyone could have it. 🙂

      • 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. the purpose of application is that we have many customers in different cities and we have many branches in many cities so we calculate the latitude and longitude of address of both customer and branch offices, now i want to achieve that using power view map it displays the result when i click on customer order and location wise it tells the distance from all where houses and from that we classify that which is branch is near to Customer address so that we can say that branch for delivery. if it is possible in excell then please reply and help me .
        thanks for your anticipation.

Generic filters
Exact matches only
Filter by Custom Post Type


Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...