Power Query Excel 2010 Tutorial Pdf

Written by Puneet for Excel 2010, Excel 2013, Excel 2016, Excel 2019

If you lot are ane of those people who work with data a lot, you can be anyone (Accountant, HR, Data Analyst, etc.), power query can exist your power tool.

Let me come straight to the signal, Power Query is one of the advanced Excel skills that you need to larn and in this tutorial, yous will be exploring power query in detail and will be learning to transform data with it.

Let's get started.

What is Excel Ability Query

Ability Query is an Excel add together-in that yous can use for ETL. That ways, you can extract data from unlike sources, transform it, and and so load it to the worksheet. Yous tin can say Power QUERY is a data cleansing automobile as it has all the options to transform the data. It is real-time and records all the steps that you perform.

Why Should Yous Use Power Query (Benefits)?

If y'all take this question in your mind, hither'southward my answer for yous:

• Different Information Sources: Yous can load information into power query editor from unlike information sources, similar, CSV, TXT, JSON, etc.
• Transform Data Hands: Normally you employ formulas and pivot tables for data transformations but with Ability QUERY you can do a lot matter just with clicks.
• It'southward Real-Time: Write a query for once and yous can refresh it every fourth dimension there is a modify in data, and it will transform the new data which you have updated.

Let me share an example:

Imagine you take 100 Excel files that take information from 100 cities and now your boss wants you to create a report with all the data from those 100 files. OKAY, if you decide to open each file manually and copy and paste data from those files and you need at least one hour for this.

But with the power query, yous can do information technology in minutes. Feeling excited? Good.

Further in this tutorial, you will learn how to use Power Query with a lot of examples, just first, you need to sympathize its concept.

The Concept of Ability Query

To larn power query, you lot need to empathize its concept that works in three steps:

1. Go Information

Power query allows you lot to get data from unlike sources like web, CSV, text files, multiple workbooks from a binder, and a lot of other sources where nosotros can store data.

2. Transform Data

Afterwards getting data in the power query you lot have a whole agglomeration of options that you tin use to transform information technology and clean it. Information technology creates queries for all the steps you perform (in a sequence one footstep after another).

3. Load Data

From the power query editor, yous can load the transformed information to the worksheet, or you can directly create a pivot table or a pivot chart or create a information connexion only.

Where is Power Query (How to Install it)?

Below you can see how to install admission to the ability query in the different versions of Microsoft Excel.

Excel 2007

If you are using Excel 2007, I'thou lamentable PQ is not available for this version so you demand to upgrade to the latest version of Excel (Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

Excel 2010 and Excel 2013

For 2010 and 2013, you demand to install an add together-in separately which you lot can download from this link and in one case yous install information technology, you'll go a new tab in the Excel ribbon, like below:

• First, download the add-in from here (Microsoft's Official Website).
• One time you have downloaded the file, open up information technology and follow the instructions.
• Subsequently that, you'll automatically get the "Power Query" tab on your Excel ribbon.

If somehow that "Ability QUERY" tab doesn't appear, at that place is no need to worry almost it. You lot can add together it using the COM Add together-ins choice.

• Get to File Tab ➜ Options ➜ Add-ins.

• In "Add together-In" options, select "COM Add-ins" and click Become.
• After that, tick marking "Microsoft Power Query for Excel".

• In the end, click OK.

Excel 2016, 2019, Office 365

If you are using Excel 2016, Excel 2019, or you take OFFICE 365 subscription, it'southward already at that place on the Data tab, every bit a group named "Become & TRANSFORM" (I like this name, do you?).

Excel Mac

If you lot are using Excel in Mac I'm afraid that there is no ability query add together-in for it and you lot can only refresh an existing query but you can't create a new one and or fifty-fifty edit a query (LINK).

Power Query Editor

Power Query has its ain editor where you can get the information, perform all the steps to create queries, and then load it to the worksheet. To open the power query editor, you lot need to go to the Data Tab and in the Become & Transform ➜ Go Data ➜ Launch Power Query Editor.

Beneath is the commencement look at the editor which you will get when you open it.

Now, let's explore each section in detail:

ane. Ribbon

Let's expect at all the available tabs:

• File: From the file tab, you can load the data, discard the editor, and open up the query settings.
• Home: In the HOME Tab, you accept options to manage the loaded data, like, delete and move columns and rows.
• Transform: This tab has all the options which you need to transform and make clean the information, like merge columns, transpose, etc.
• Add together Cavalcade: Here yous have the option to add new columns to the data yous have in the power editor.
• View: From this tab, yous can brand changes to the view for the power query editor and data loaded.

2. Applied Steps

On the right side of the editor, you take a query setting pane which includes the proper noun of the query and all the practical steps in a sequence.

When you lot right-click on a step y'all have a list of options that you lot tin can perform, like, rename, delete, edit, move up or down, etc. and when you click on a step, the editor volition take you to the transformation washed on that step.

Await at the below where yous have the total five steps practical and when I click on the 4th step it takes me to step four'south transformation where the columns name hasn't changed.

three. Queries

The queries pane on the left side lists all the queries you have in the workbook correct now. It's basically one place where you can manage all the queries.

When you lot right-click on a query name you lot tin come across all the options that you can use (copy, delete, duplicate, etc.)

Yous can also create a new query by simply correct click on the bare space on the queries pane then select the selection for the data source.

iv. Formula Bar

As I said, whenever you apply a step in the editor it generates 1000 code for that step, and you can see that code in the formula bar. Yous can but click on the formula bar to edit the code.

Once you learn to use M code you can also create step by writing the code and but clicking on the "FX" button to enter a custom footstep.

5. Data Preview

The information preview area looks like an Excel worksheet but in that location'south a little different than a normal worksheet where y'all can edit a prison cell or data directly. When you load data into the editor (we will do it in a while) information technology shows all the columns with the headers with the columns proper noun so rows with information.

At the height of each column, you can run across the information type of the data in the cavalcade. When you load information into the editor the ability query applies the right data type (almost every time) to each cavalcade automatically.

Y'all can click on the acme left button on the column header to change the data type applied to the column. It has a list of all the data types from where you tin.

And on the left side of the column header there you take the filter button which you tin employ to filter values from the column. Notation: When y'all filter values from a column, the ability query takes it equally one footstep and lists it in the applied steps.

If you correct-click on the header of the column you can see that in that location is a menu that includes a list of the options which you tin utilise to transform the data and use whatsoever of the options and PQ stores information technology equally a step in the applied steps.

Data Sources for Power Query

The all-time role of the ability query is you lot have the pick to get data from multiple sources and transform that information and then load it into the worksheet. When you click on the Go Data in the GET & TRANSFORM you tin can see the complete listing of data sources that yous tin become information load into the editor.

Now let'southward look at some of the data sources:

• From Table/Range: With this option, you can load data into the power query editor directly from the active worksheet.
• From Workbook: From a different workbook that you take on your reckoner. You simply need to locate that file using an open dialog box and it will become data from that file automatically.
• From Text/CSV: Get data from a text file or a comma-separated file and then yous can load it into the worksheet.
• From Folder: It takes all the files from the folder and load data from them into the power query editor. (Come across this: Combine Excel Files from a Folder).
• From Web: With this option, you go data from a spider web accost, imagine you take a File that is stored on the web or you accept a web page from where yous need to get the information.

How to Load Data into Power Query Editor

Now allow'due south learn to load information into the power query editor. Here yous have a list of educatee names and their scores (LINK).

You will exist loading data directly from the worksheet, and so you lot need to open the file first and then follow the below steps:

• First, apply an Excel table to the data (Even if you lot don't exercise information technology Excel volition do information technology for y'all before loading data into PQ editor).
• At present, select a prison cell from the table and click on the "From Table/Range" (Data Tab Get & Transform).

• Once you click on the button, Excel confirms the range of information to apply an Excel table to it.

• At this point, you accept the data into the power query editor, and information technology looks something like beneath.

• Here you tin see:
  • In the Formula bar, PQ has generated the M code for the table you take just loaded into the editor.
  • On the left side of the editor, you take the queries pane where you take the list of the queries.
  • On the right side, in the query settings, you take the department called "Practical Steps" where you have all the steps listed. Note: You must be thinking that you lot haven't performed any "Changed Type" but at that place's a step called "Changed Type" is in that location. Let me tell you the SMARTNESS of POWER QUERY when you lot load data into the editor it checks and applies the correct data types for all the columns automatically.

Power Query Examples (Tips and Tricks)

You can learn to perform some of the basic tasks which you normally do with functional formulas in Excel, but with power query, you can do information technology with a few clicks:

1. Supercede Values

You have a list of values, and yous desire to supersede a value or some values with something else. Well, with the help of the power query you lot can create a query and replaces those values, in no time.

In the beneath list, you need to replace my proper noun "Puneet" with "Punit".

• Get-go, edit the list in the power query editor.
• After that, in the power query editor, go to "Transform Tab" and click "Replace Values".

• At present, in "Value to Find", enter "Puneet" and in "Replace With" enter "Punit" and afterward that, click OK.

• One time y'all click OK all the values get replaced with the new values and now, click on "Shut and Load" to load data in the worksheet.

2. Sort Data

Only like normal sorting, y'all tin can sort data past using ability query and I'thousand using the same name list which you take used in the in a higher place example.

• First, load data in the ability query editor.
• In the Home tab, y'all have 2 sorting buttons (Ascending and Descending).
• Click on whatever of these buttons to sort.

3. Remove Columns

Let's say you got data from somewhere and yous need to delete some columns from it. The thing is, you have to delete those columns every fourth dimension you add new data, correct? Only, power query tin accept care of this.

• Select the column or multiple columns that you want to delete.

• Now, correct-click and select "Remove".

Quick Tip: There's likewise an option to "Remove Other Columns" where yous can delete all the unselected columns.

iv. Split up Column

Simply similar the text to column pick, you have "Split Column" in power query. Let me tell you how it works.

• Select the cavalcade and go to the Home Tab ➜ Transform ➜ Carve up Column ➜ By Delimiter.

• Select the custom from the drop-downwards and enter "-" into it.
• At present, here you lot have iii unlike options to split a column.
  • Left-most Delimiter
  • Correct-most Delimiter
  • Each occurrence of the delimiter

If you accept only one delimiter in a cell, all iii volition work in the same fashion, but if you have more than than i delimiter and then you have to cull accordingly.

5. Rename a Column

You tin can simply rename a column by right click and and then click on the "Rename".

Quick Tip: Let say yous have a query for renaming a column and someone else rename it by mistake. You lot can restore that name only with a click.

6. Duplicate Column

In Power Query, there is a simple pick to create a duplicate cavalcade. All yous need to do is right-click on the column for which you desire to create a duplicate cavalcade and then click on "Duplicate Cavalcade".

seven. Transpose Cavalcade or Row

In the power query, transposing is a cup of cake. Yes, only one click.

• Once you load data into the power query editor, you just need to select the column(s) or row(southward).
• Get to Transform Tab ➜ Table ➜ Transpose.

8. Supersede/Remove Errors

Normally for replacing or removing errors in Excel yous can use find and supersede option or a VBA code. But in power query, it's a whole lot easier. Look at the below cavalcade where you have some errors and you can remove too every bit supplant them.

When you right-click on the column, you'll take both of the options.

• Supercede Errors
• Remove Errors

9. Alter Information Type

Y'all have data in a cavalcade but it's non in the right format. So, every fourth dimension y'all need to modify its format.

• Showtime, edit data into the power query editor.
• After that, select the cavalcade and go to the Transform Tab.
• At present, from data type select the "Date" as a type.

10. Add Column from Examples

In the power query, there is an option to add a sample column which is non really a sample related to the current column.

Let me give yous an case:

Imagine you need day names from a date column. Instead of using a formula or whatever other selection, you can use, yous can use the "Add Column from Examples".

Hither'south how to exercise this:

• Right-click on a column and click on "Add Column from Examples".

• Here yous'll become a blank column. Click on the beginning cell of the column to get the list of values you can insert.

• Select "Mean solar day of Week Proper noun from Date" and click OK.

Boom! your new column is here.

11. Alter Example

Yous have the following options for changing the case of text in power query.

• Lower Case
• Upper Instance
• Capitalize Each Word

You can exercise it by right click on a column and select any of the higher up iii options. Or, go to the Transform Tab ➜ Text Column ➜ Format.

12. Trim and Clean

To clear data or delete unwanted spaces you can apply TRIM and Clean options in power query. Steps are elementary:

• Right-click on a column or select all the columns if you have multiple columns.
• Go to Transform Tab ➜ Text Column ➜ Format.
  1. TRIM: To remove trailing and leading whitespaces from a cell.
  2. CLEAN: To remove not-printable characters from a prison cell.

13. Add Prefix/Suffix

If yous are a formula savvy, and then I'thou sure you concord with me that extracting text or number from a prison cell requires to combine different functions. But power query has solved a lot of these things in a good way. You have 7 means to excerpt values from a cell.

fifteen. Merely Date or Time

It happens a lot of times that you have date and time, both in a single jail cell, but needs one of them.

• Select the column where y'all take the date and time combined.
• If you want:
  • Date: Right Click ➜ Transform ➜ Date Only.
  • Time: Right Click ➜ Transform ➜ Time Merely.

16. Combine Date and Time

At present you know how to split date and time. Merely the next you lot demand to know how to combine them.

• First, select the date column and click on the "Engagement But" pick.
• After that, select both columns (Appointment and Time) and go to the transform tab and from the "Appointment and Fourth dimension Column" Group become to Engagement and click "Combine Date and Time".

17. Rounding Numbers

Hither are the following options which you have for rounding numbers.

• Round Down: To round downwardly a number.
• Round Up: To round upwards a number.
• Round: Y'all tin can choose upward to how my decimals you lot can round.

Here are the steps:

• Select the cavalcade and correct-click ➜ Transform ➜ Round.
  1. Round Down: To round down a number.
  2. Round Upward: To round up a number.
  3. Round: Y'all can choose up to how my decimals you can round.

Note: When you select the "#three Round" option you demand to enter the number of decimals to round.

18. Calculations

There are options that y'all tin can utilise to perform calculations (a lot of). You can notice all these options on the Transform Tab (in Number Column group).

• Basic
• Statistics
• Scientific
• Trigonometry
• Rounding
• Information

To perform whatever of this calculation you demand to select the column and then the pick.

19. Grouping by

Allow'southward say you lot take a large data set up and you desire to create a summary table. Here's what you need to do:

• In the Transform tab, click on the 'Group by" button and yous'll get a dialog box.

• Now, from this dialog box select the column with which you want to group and after that, add a name, select the operation, and the column where you have values.

• In the end, click OK.

Note: There are also some advanced options in the "Group by" option which you lot can utilize to create a multi-level grouping tabular array.

twenty. Remove Negative Values

In i of my blog posts, I have listed 7 methods to remove the negative signs and the power query is one of them. Just right click on a column and get to transform option and then click on "Absolute value".

This instantly removes all the negative signs from the values.

How to Load Information Back to the Worksheet

One time you transform your information, you tin can load it to the worksheet and use information technology for further analysis. On the abode tab there is a button called "Close and Load" when you click on it you get a drop-down which has options further:

• Close and Load
• Shut and Load To

• Once y'all click on the push, it will bear witness the following options:

• Select how you desire to view this data in your worksheet.
  • Tabular array
  • Pivot Tabular array Written report:
  • Pivot Nautical chart
  • Just Create Connection
  • Where exercise you want to put the Information?
    • Existing Worksheet
    • New Worksheet.
  • Add this data to the Information Model.
• But select the tabular array option and new worksheet and don't tick mark the data model and click OK.

• The moment y'all click OK, it adds a new worksheet with the data.

More Examples to Learn

Auto Refresh a Query

From all the examples that I have mentioned hither, this one is the most of import. When you lot create a query, yous tin can make it auto-refresh (you can set a timer).

And here are the steps:

• On the Data tab, click on "Queries & Connections" and you'll get the Queries and Connection pane on the right side of the window.

• Now, right-click on the query and tick mark "Refresh every" and enter the minutes.

How to use a Formula and a Function in Ability Query

But similar y'all can use functions and formulas in Excel worksheet, the power query has its own list of functions that you tin utilize. The basics of part and formulas in power query are the same equally Excel's worksheet functions.

In PQ, you need to add together a new custom column to add a role or a formula.

Let's have an example: In the beneath information (already in the PQ editor) you lot have the outset name and last proper noun (DOWNLOAD LINK).

Imagine you need to merge both names and create a cavalcade for the full name. In this case, you tin enter a simple formula to concatenate names from both columns.

• First, go to the Add Column tab and click on the "Custom Column".

• Now in the custom cavalcade dialog box, enter the proper name of the new column "Total Proper name" or annihilation yous want to proper name the new column.

• The custom column formula is the place where you demand to enter the formula. So enter the beneath formula in it:

[First Name]&" "&[Last Name]

• When you enter a formula in the "custom column formula", PQ verify the formula that yous have entered and shows a bulletin "No syntax error have been detected" and if in that location'due south an mistake information technology will prove an error bulletin based on the blazon of the error.
• Once yous enter the formula and that formula doesn't accept any errors in it, merely press OK.
• Now you accept a new column at the end of the data which has values from 2 columns (first name and the last proper noun).

How to use a Function in Power Query

In the same manner, yous tin besides utilise a function while calculation a custom column and Ability Query has a huge list of functions that you can use.

Let'due south sympathise how to apply a function with an easy and elementary example. I'm continuing the above example where nosotros take added a new column past combining the starting time name and terminal proper noun.

But now, y'all need to convert that full name text which you have in that column into the upper case. The role which you tin can employ is "Text.Upper". As the proper noun suggests, it converts a text to an upper-case text.

• Kickoff, go to the add cavalcade tab and click on the custom column.

• Now in the custom column dialog box, enter the column name and below formula in the custom cavalcade formula box:

Text.Upper([Full Name])

• And when you click OK information technology creates a new column with all the names in the upper-case letter.
• The next thing is to delete the old cavalcade and rename the new column. So correct-click on the showtime cavalcade and select remove.

• In the end, rename the new column his "Total Proper name".

In that location are a total of 700 functions that you tin can utilise in power query while adding a new column and here is the complete list provided by Microsoft for these functions, do check them out.

How to Edit a Query in PQ

If you want to make some changes in the query which is already in your workbook you tin can only edit it and then make those changes. On the Data tab, there's a button named Queries and Connections.

When y'all click on this push, it opens a pane on the right side that lists all the queries that you have in the current workbook.

You tin can correct-click on the query proper noun and select edit and yous volition get it in the power query editor to edit.

When you edit a query, you lot tin see that all the steps which you have performed earlier are listed in the "Applied Steps" that you tin also edit or you can perform new steps.

And once you are done with your changes y'all can simply click on the "Close & Load" push.

Export and Import Connections

If y'all have a connection which you take used for a query and now you lot want to share that connexion with someone else, you can export that connection as an odc file.

On the query table, at that place's a button called "Consign Connection" and when you click on information technology, information technology allows yous to save that query'south connection in your arrangement.

And if you desire to import a connectedness that is shared by someone else, you lot can simply go to the Data tab and in the Get & Transform click on the existing connections.

And and so click on the "Scan for More than" button from where you can locate the connectedness file which has been shared with you and import it to your workbook.

Power Query Language (One thousand Lawmaking)

As I mentioned before that for every step you performed in power query information technology generates a lawmaking (at the backend) which is called M Code. On the Home tab, there is a push button called "Advanced Editor" which you can utilize to see the code.

And when you lot click on the advanced editor it will show you the code editor and that lawmaking looks something like below:

M is a case sensitive linguistic communication and like all the other languages it uses variables and expressions. The bones structure of code looks like below where the code starts with the LET expression.

In this code, we have two variables and the values defined to them. In the end, to become the value, IN expression has been used. Now when yous click OK it will return the value assigned to the variable "Variablename" in the result.

Check out this resource to learn more well-nigh Power Query Language.

In the End

What is Excel Power Query?

Power Query is a information transforming engine which you can use to get data from multiple sources, clean and transform that data and and then use information technology farther in the analysis.

Y'all can't afford to avoid the POWER QUERY. If you think like this, a lot of things which we do with Excel functions or VBA codes can be automated using information technology, and I'1000 sure this tutorial inspires you to use it more and more.

Just now you need to tell me one thing. Which thing do you lot like most about the POWER QUERY?

Y'all must check out these tutorials

• Create a Pivot Tabular array from Multiple Files

Source: https://excelchamps.com/power-query/

Posted by: manninosumanducke.blogspot.com

Share this post