Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus
66

Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool

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, Salesforce.com, 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, Salesforce.com, 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

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.

Power Query Workshop

The workshop includes 2 days of live training and a 3rd day with a live Q&A session.

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. Ken and Miguel literally wrote the book on Power Query, its called “M is for Data Monkey” and you get a free copy when you register.

The workshop starts on November 16th, and all sessions will be recorded and included with your registration.

Click here to learn more and register for the Power Query Workshop

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, M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query.

I also find myself on Chris Webb’s blog a lot at https://cwebbbi.wordpress.com/

I also just read Chris Webb’s book on Power Query and learned a ton!  It’s a great resource for Power Query.  Here is my affiliate link to Amazon.

Power Query for Power BI and 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.

Thanks!

Please share
Jon Acampora
 

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. :) more about me

Click Here to Leave a Comment Below 66 comments
Asanka - July 10, 2017

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

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

Reply
Amit - April 9, 2017

Hi

I am wondering if i can use power query feature to download all the data from 4 expendable sections (Key Stats, Annual, Quarter & market data) for the stocks on webpage ..

http://www.moneycontrol.com/stocks/data-bank/standalone/steel-large.html

I tried but so far no luck … 🙁

Reply
harsha547 - April 2, 2017

Thanks for sharing !!

Reply
Yogiraj - February 22, 2017

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 ?

Reply
    Yogiraj - February 22, 2017

    Sorry HTML format is not showing properly

    Find Here probably

    “””” to “”<td"" simple replace thing

    Reply
      Yogiraj - February 22, 2017

      Again its not proper
      I want to make

      “td class:…” to “td”
      “tr class:…” to “tr”

      Reply
Yogiraj - February 21, 2017

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 ?

Reply
JTR - February 13, 2017

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

Reply
    Jon Acampora - February 15, 2017

    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!

    Reply
Kevin D. - Las Vegas - January 30, 2017

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

Reply
    Jon Acampora - February 15, 2017

    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.

    Reply
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

Reply
    Jon Acampora - February 4, 2017

    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.

    Reply
      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.
      Great!

      Georg

      Reply
Yogiraj - December 18, 2016

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”

Thanks
Yogiraj

Reply
Marius - October 7, 2016

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

Reply
    Jon Acampora - October 11, 2016

    Hi Marius,
    It would be hard for me to tell without seeing the data. You might want to try posting to the Microsoft Answers forum if you think it is a bug.

    Reply
Vishnu - September 23, 2016

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.)
2.is there any way that i can build my query in excel cell with all my dynamic filters and use this query in power query?

Thanks,
Vishnu.

Reply
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?
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.

Thanks

Reply
    Jon Acampora - August 26, 2016

    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.

    Thanks!

    Reply
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. 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?
Thanks.

Reply
    Jon Acampora - July 31, 2016

    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?

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

Cristina

Reply
    Jon Acampora - July 31, 2016

    Hi Cristina,
    I haven’t experienced this error with SalesForce. You might want to try posting this in the Microsoft Answers forum. Thanks!

    Reply
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

Reply
Rodney - April 12, 2016

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

Reply
    Jon Acampora - April 13, 2016

    Hi Rodney,

    I can recommend two books. Both “M for Data Monkey” and “Power Query for PowerBI and Excel” are great books. You can read my reviews about these books on my resources page. I hope that helps.

    Thanks and have a great day!

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

Reply
    Jon Acampora - March 16, 2016

    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 PowerBi.com. This tool basically combines PowerPivot and PowerQuery, and allows the users to view and interact with your dashboards online at PowerBi.com. 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 PowerBi.com 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.

    Thanks!

    Reply
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

Reply
    Jon Acampora - 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.

    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!

    Reply
Abiral - February 26, 2016

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?

Reply
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

Reply
    Jon Acampora - February 18, 2016

    Hi Yogiraj,
    Power Query will not be able to output to a CSV file. It outputs to an Excel Table in the Excel file. I hope that helps.

    Reply
Oliver - February 3, 2016

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?

Thanks!

Reply
    Jon Acampora - February 3, 2016

    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!

    Reply
      Oliver - February 3, 2016

      Thanks! We’re all on 2013, but not too much of an issue (only talking a few people.

      Reply
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

Reply
    Jon Acampora - 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. 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!

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

      Reply
        Jon Acampora - January 6, 2016

        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.

        Reply
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 (?) 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?

Regards,

Chris.

Reply
Dimitar Petrov - September 17, 2015

Perfect tolls Quary, View, Map

Reply
Diana - September 1, 2015

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?

Reply
    Jon Acampora - September 2, 2015

    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.

    Thanks!

    Reply
Adding Excel’s “Power Query” to Your Arsenal | Smart Fantasy Baseball - August 24, 2015

[…] of demonstrating how you can really fine tune and clean up the data you import through Power Query. Interesting Examples that Might Apply to Baseball DataThis is a fairly lengthy post, but look specifically for the sections labelled “Append […]

Reply
stephen woodward - August 11, 2015

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.

Reply
    Jon Acampora - August 14, 2015

    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!

    Reply
Dave Bruns - March 25, 2015

Beautiful overview, Jon. Well done!

Looking forward to taking Power Query for a spin soon…

Reply
Mynda - March 24, 2015

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.

Mynda

Reply
    Jon Acampora - March 24, 2015

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

    Reply
Roger Govier - March 24, 2015

Jon
Absolutely superb introduction to the latest “hidden gem ” within Excel.
Keep up the good work.

Reply
    Jon Acampora - March 24, 2015

    You made my day Roger! Thanks and great to hear from you. 🙂
    “Hidden gem” is the perfect term for Power Query.

    Reply
pmsocho - March 24, 2015

Great and comprehensive introduction to Power Query.
Thanks for this.

Reply
Jeff Weir - March 23, 2015

What a freakin awesome intro, Jon. I’m sticking a link to this in the book…

Reply
    Jon Acampora - March 24, 2015

    Thanks Jeff! Now I really can’t wait for the book. 😉

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

      Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x