How To Set Up Formulas For A Checkbook Register In Excel 2016

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

If yous are one of those people who piece of work with data a lot, you can be anyone (Accountant, HR, Information Analyst, etc.), ability query can be your power tool.

Let me come straight to the point, Power Query is one of the advanced Excel skills that you need to learn and in this tutorial, you lot volition be exploring ability query in detail and will be learning to transform information with it.

Let'southward get started.

What is Excel Power Query

Power Query is an Excel add together-in that you can utilize for ETL. That means, you tin can extract information from dissimilar sources, transform it, and and so load information technology to the worksheet. You tin can say POWER QUERY is a information cleansing machine equally it has all the options to transform the data. It is real-fourth dimension and records all the steps that y'all perform.

Why Should You lot Apply Power Query (Benefits)?

If you accept this question in your mind, here's my answer for you lot:

• Dissimilar Data Sources: You can load information into ability query editor from unlike data sources, like, CSV, TXT, JSON, etc.
• Transform Data Easily: Commonly you lot use formulas and pivot tables for data transformations but with POWER QUERY you can exercise a lot thing merely with clicks.
• Information technology's Real-Fourth dimension: Write a query for in one case and you can refresh it every time there is a change in data, and information technology will transform the new data which y'all have updated.

Permit me share an example:

Imagine you lot have 100 Excel files that have information from 100 cities and now your dominate wants you to create a study with all the information from those 100 files. OKAY, if you determine to open up each file manually and copy and paste data from those files and you need at to the lowest degree i hour for this.

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

Further in this tutorial, y'all will larn how to use Power Query with a lot of examples, merely beginning, you need to understand its concept.

The Concept of Ability Query

To larn power query, you need to understand its concept that works in 3 steps:

1. Get Information

Power query allows y'all to get data from dissimilar sources like web, CSV, text files, multiple workbooks from a folder, and a lot of other sources where nosotros can store data.

2. Transform Data

After getting data in the power query you have a whole bunch of options that y'all can use to transform it and clean it. It creates queries for all the steps you lot perform (in a sequence one footstep later another).

three. Load Information

From the power query editor, you tin can load the transformed data to the worksheet, or you tin can direct create a pin tabular array or a pivot chart or create a information connection only.

Where is Ability Query (How to Install it)?

Below y'all can see how to install access to the power query in the different versions of Microsoft Excel.

Excel 2007

If yous are using Excel 2007, I'1000 sad PQ is not bachelor for this version so you lot need 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 need to install an add together-in separately which you can download from this link and once you install it, you'll get a new tab in the Excel ribbon, like beneath:

• First, download the add-in from here (Microsoft's Official Website).
• Once you take downloaded the file, open it and follow the instructions.
• After that, you'll automatically get the "Ability Query" tab on your Excel ribbon.

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

• Go to File Tab ➜ Options ➜ Add together-ins.

• In "Add together-In" options, select "COM Add-ins" and click Go.
• After that, tick mark "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 have Role 365 subscription, information technology'southward already there on the Data tab, as a group named "Go & TRANSFORM" (I similar this name, do y'all?).

Excel Mac

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

Power Query Editor

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

Below is the first expect at the editor which y'all will get when you open information technology.

At present, let's explore each section in detail:

1. Ribbon

Let'south look at all the available tabs:

• File: From the file tab, you can load the data, discard the editor, and open the query settings.
• Abode: In the Home Tab, you have options to manage the loaded data, like, delete and motility columns and rows.
• Transform: This tab has all the options which you need to transform and make clean the data, like merge columns, transpose, etc.
• Add Column: Here you lot accept the pick to add new columns to the data you lot have in the power editor.
• View: From this tab, you tin can make changes to the view for the power query editor and data loaded.

2. Applied Steps

On the right side of the editor, yous have a query setting pane which includes the proper noun of the query and all the applied steps in a sequence.

When you right-click on a step you lot have a list of options that yous tin perform, like, rename, delete, edit, motion up or downward, etc. and when you click on a stride, the editor volition take you lot to the transformation done on that step.

Expect at the below where y'all have the total five steps applied and when I click on the 4th step it takes me to step 4'due south transformation where the columns proper noun hasn't changed.

3. Queries

The queries pane on the left side lists all the queries y'all have in the workbook correct now. It'due south basically i place where you tin can manage all the queries.

When you correct-click on a query proper name you can see all the options that yous tin can utilize (copy, delete, duplicate, etc.)

Yous tin can too create a new query by but right click on the bare space on the queries pane and then select the option for the data source.

four. Formula Bar

As I said, whenever you lot apply a pace in the editor information technology generates M code for that stride, and you can see that code in the formula bar. You can simply click on the formula bar to edit the code.

One time you lot larn to use Thousand code you can too create step past writing the code and but clicking on the "FX" button to enter a custom step.

5. Data Preview

The data preview surface area looks like an Excel worksheet but there'south a picayune different than a normal worksheet where you tin can edit a prison cell or data directly. When yous load information into the editor (we will do it in a while) it shows all the columns with the headers with the columns name and then rows with data.

At the top of each cavalcade, you can run into the data type of the information in the cavalcade. When you load information into the editor the power query applies the correct data type (nearly every time) to each column automatically.

You tin click on the summit left button on the column header to change the data type practical to the column. It has a list of all the information types from where you can.

And on the left side of the cavalcade header there you lot have the filter button which you can use to filter values from the column. Annotation: When you lot filter values from a column, the ability query takes it equally one pace and lists it in the applied steps.

If you right-click on the header of the column you tin see that there is a menu that includes a list of the options which y'all can use to transform the information and use whatever of the options and PQ stores it as a step in the practical steps.

Information Sources for Power Query

The best part of the power query is you accept the option to get data from multiple sources and transform that data and then load it into the worksheet. When you click on the Get Information in the GET & TRANSFORM you tin can encounter the complete list of data sources that y'all can get data load into the editor.

Now let's look at some of the information sources:

• From Table/Range: With this option, you lot tin can load information into the power query editor straight from the active worksheet.
• From Workbook: From a different workbook that you take on your computer. Yous just need to locate that file using an open dialog box and information technology will become data from that file automatically.
• From Text/CSV: Get data from a text file or a comma-separated file and so you can load it into the worksheet.
• From Binder: Information technology takes all the files from the folder and load data from them into the power query editor. (Encounter this: Combine Excel Files from a Folder).
• From Web: With this selection, y'all get data from a web address, imagine you have a File that is stored on the web or you have a web folio from where you need to get the data.

How to Load Data into Power Query Editor

Now let's learn to load information into the power query editor. Here yous have a list of student names and their scores (LINK).

You will be loading data directly from the worksheet, so y'all need to open the file beginning and then follow the beneath steps:

• First, use an Excel table to the data (Even if you don't do it Excel volition do information technology for yous before loading information into PQ editor).
• Now, select a prison cell from the tabular array and click on the "From Table/Range" (Data Tab Become & Transform).

• Once you click on the button, Excel confirms the range of data to utilise an Excel tabular array to it.

• At this point, you take the information into the power query editor, and it looks something similar below.

• Here you can meet:
  • In the Formula bar, PQ has generated the K lawmaking for the table you have just loaded into the editor.
  • On the left side of the editor, you take the queries pane where you have the list of the queries.
  • On the right side, in the query settings, you have the department called "Applied Steps" where you take all the steps listed. Notation: You must be thinking that yous haven't performed any "Changed Type" but in that location's a step called "Changed Type" is there. Let me tell you lot the SMARTNESS of Ability QUERY when you 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 ordinarily do with functional formulas in Excel, only with power query, you can do it with a few clicks:

1. Replace Values

You lot take a list of values, and you desire to replace a value or some values with something else. Well, with the help of the ability query you lot tin can create a query and replaces those values, in no time.

In the below list, you demand to replace my name "Puneet" with "Punit".

• First, edit the list in the ability query editor.
• Later that, in the power query editor, go to "Transform Tab" and click "Replace Values".

• Now, in "Value to Find", enter "Puneet" and in "Supplant With" enter "Punit" and after that, click OK.

• In one case you lot click OK all the values get replaced with the new values and now, click on "Close and Load" to load data in the worksheet.

2. Sort Data

But like normal sorting, you can sort data by using ability query and I'm using the aforementioned name list which you have used in the in a higher place example.

• First, load data in the power query editor.
• In the Abode tab, you have 2 sorting buttons (Ascending and Descending).
• Click on whatever of these buttons to sort.

three. Remove Columns

Let'southward say y'all got information from somewhere and you need to delete some columns from it. The thing is, you have to delete those columns every fourth dimension you add new data, correct? But, power query can accept care of this.

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

• At present, right-click and select "Remove".

Quick Tip: At that place's as well an pick to "Remove Other Columns" where you lot tin can delete all the unselected columns.

4. Split Column

Just similar the text to column option, you take "Split Column" in power query. Let me tell you how information technology works.

• Select the column and become to the Home Tab ➜ Transform ➜ Split Cavalcade ➜ By Delimiter.

• Select the custom from the drop-down and enter "-" into information technology.
• Now, here you have three unlike options to split a cavalcade.
  • Left-most Delimiter
  • Right-nigh Delimiter
  • Each occurrence of the delimiter

If you have simply one delimiter in a jail cell, all three will piece of work in the same manner, but if you take more than one delimiter so you accept to cull accordingly.

5. Rename a Column

You can simply rename a column by correct click so click on the "Rename".

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

vi. Duplicate Column

In Power Query, there is a simple selection to create a indistinguishable column. All you need to do is correct-click on the column for which you lot want to create a duplicate cavalcade and so click on "Indistinguishable Column".

vii. Transpose Column or Row

In the power query, transposing is a loving cup of cake. Yep, just 1 click.

• Once yous load data into the power query editor, y'all but need to select the column(s) or row(due south).
• Go to Transform Tab ➜ Table ➜ Transpose.

8. Replace/Remove Errors

Normally for replacing or removing errors in Excel you lot can employ discover and supplant choice or a VBA code. Merely in power query, it's a whole lot easier. Look at the beneath cavalcade where you have some errors and y'all can remove as well as replace them.

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

• Replace Errors
• Remove Errors

9. Change Data Blazon

You lot take data in a column but it'south non in the correct format. Then, every time you demand to change its format.

• Offset, edit data into the power query editor.
• Afterwards that, select the column and go to the Transform Tab.
• Now, from data type select the "Engagement" as a blazon.

10. Add Cavalcade from Examples

In the ability query, there is an option to add a sample column which is not really a sample related to the current cavalcade.

Let me give you an example:

Imagine you demand day names from a date column. Instead of using a formula or whatsoever other option, you can use, you tin use the "Add Column from Examples".

Here'south how to do this:

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

• Here you'll get a blank cavalcade. Click on the commencement cell of the column to get the list of values y'all can insert.

• Select "Solar day of Week Proper name from Date" and click OK.

Boom! your new column is here.

11. Change Case

You have the following options for changing the instance of text in power query.

• Lower Case
• Upper Case
• Capitalize Each Word

You can do it by right click on a column and select whatsoever of the in a higher place three options. Or, go to the Transform Tab ➜ Text Column ➜ Format.

12. Trim and Clean

To articulate data or delete unwanted spaces you can employ TRIM and Make clean options in ability query. Steps are simple:

• Correct-click on a column or select all the columns if yous take multiple columns.
• Go to Transform Tab ➜ Text Cavalcade ➜ Format.
  1. TRIM: To remove abaft and leading whitespaces from a cell.
  2. CLEAN: To remove non-printable characters from a jail cell.

xiii. Add Prefix/Suffix

If you are a formula savvy, then I'm certain you agree with me that extracting text or number from a cell requires to combine different functions. But power query has solved a lot of these things in a good way. You have vii ways to extract values from a cell.

fifteen. Only Date or Time

Information technology happens a lot of times that you have engagement and time, both in a single cell, but needs ane of them.

• Select the cavalcade where you have the date and time combined.
• If yous desire:
  • Date: Right Click ➜ Transform ➜ Appointment Simply.
  • Time: Right Click ➜ Transform ➜ Time Only.

16. Combine Date and Time

Now you know how to separate appointment and time. Merely the side by side you need to know how to combine them.

• First, select the appointment column and click on the "Appointment Only" option.
• Afterwards that, select both columns (Date and Fourth dimension) and go to the transform tab and from the "Date and Time Column" Grouping go to Date and click "Combine Appointment and Fourth dimension".

17. Rounding Numbers

Here are the post-obit options which you have for rounding numbers.

• Circular Down: To round downwardly a number.
• Circular Up: To round upward a number.
• Round: You tin can cull upwardly to how my decimals you can round.

Hither are the steps:

• Select the column and right-click ➜ Transform ➜ Round.
  1. Round Downwards: To circular down a number.
  2. Round Upwardly: To round upward a number.
  3. Round: You tin can cull upwardly to how my decimals you lot tin round.

Annotation: When y'all select the "#3 Round" option yous need to enter the number of decimals to circular.

18. Calculations

There are options that you can employ to perform calculations (a lot of). You can find all these options on the Transform Tab (in Number Column group).

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

To perform any of this calculation you need to select the cavalcade and then the choice.

xix. Group by

Allow's say yous have a large information prepare and you desire to create a summary table. Here's what you need to practise:

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

• At present, from this dialog box select the column with which you desire to group and after that, add together a proper noun, select the performance, and the column where you take values.

• In the end, click OK.

Note: In that location are also some advanced options in the "Group by" option which you can apply to create a multi-level group table.

20. Remove Negative Values

In one of my weblog posts, I have listed vii methods to remove the negative signs and the ability query is one of them. But correct click on a column and become to transform choice and and so 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 data, you lot tin can load it to the worksheet and use it for farther analysis. On the abode tab in that location is a button called "Close and Load" when yous click on it yous get a drop-downward which has options further:

• Shut and Load
• Close and Load To

• Once yous click on the button, it will show the following options:

• Select how you want to view this data in your worksheet.
  • Table
  • Pivot Tabular array Written report:
  • Pivot Chart
  • Only Create Connection
  • Where exercise y'all want to put the Information?
    • Existing Worksheet
    • New Worksheet.
  • Add this data to the Information Model.
• Just select the table choice and new worksheet and don't tick mark the data model and click OK.

• The moment yous click OK, information technology adds a new worksheet with the data.

More Examples to Learn

Auto Refresh a Query

From all the examples that I accept mentioned here, this one is the virtually important. When you lot create a query, you tin can make it auto-refresh (yous 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, correct-click on the query and tick mark "Refresh every" and enter the minutes.

How to use a Formula and a Function in Power Query

Just similar you lot can use functions and formulas in Excel worksheet, the ability query has its own list of functions that y'all tin use. The nuts of function and formulas in ability query are the same every bit Excel'southward worksheet functions.

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

Let'southward take an case: In the below data (already in the PQ editor) you accept the first proper noun and final proper noun (DOWNLOAD LINK).

Imagine you demand to merge both names and create a column for the full proper name. In this example, you lot can enter a unproblematic formula to concatenate names from both columns.

• Start, get to the Add together Column tab and click on the "Custom Column".

• Now in the custom column dialog box, enter the proper name of the new column "Total Name" or anything you lot want to name the new column.

• The custom column formula is the place where you lot need to enter the formula. So enter the below formula in information technology:

[Kickoff Name]&" "&[Last Name]

• When you enter a formula in the "custom cavalcade formula", PQ verify the formula that you have entered and shows a message "No syntax fault take been detected" and if there'due south an error it will prove an error message based on the type of the error.
• In one case you enter the formula and that formula doesn't have any errors in it, only printing OK.
• Now you lot accept a new column at the stop of the information which has values from two columns (showtime proper noun and the last name).

How to utilise a Function in Ability Query

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

Permit's understand how to employ a function with an easy and simple example. I'one thousand continuing the above example where we have added a new column past combining the first name and terminal name.

But now, yous need to convert that total name text which you have in that column into the upper case. The function which you can use is "Text.Upper". Equally the proper name suggests, it converts a text to an upper-case text.

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

• Now in the custom column dialog box, enter the column proper noun and below formula in the custom column formula box:

Text.Upper([Total Name])

• And when you click OK it creates a new cavalcade with all the names in the uppercase.
• The next matter is to delete the old column and rename the new column. And then right-click on the first column and select remove.

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

There are a full of 700 functions that you can utilise in power query while calculation a new column and here is the complete listing 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 yous can just edit it and then make those changes. On the Data tab, there'due south a button named Queries and Connections.

When you click on this button, it opens a pane on the right side that lists all the queries that you take in the electric current workbook.

You tin can correct-click on the query name and select edit and you will get it in the ability query editor to edit.

When you edit a query, you can see that all the steps which y'all accept performed before are listed in the "Applied Steps" that y'all can also edit or you can perform new steps.

And one time you are washed with your changes you tin can simply click on the "Shut & Load" button.

Export and Import Connections

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

On the query table, there'due south a button called "Export Connexion" and when you click on it, information technology allows you to salvage that query'due south connection in your arrangement.

And if you desire to import a connection that is shared past someone else, you can only become to the Data tab and in the Get & Transform click on the existing connections.

So click on the "Browse for More than" button from where you lot tin can locate the connectedness file which has been shared with you lot and import it to your workbook.

Ability Query Linguistic communication (M Code)

As I mentioned before that for every step you performed in power query it generates a code (at the backend) which is called M Code. On the Home tab, there is a push called "Advanced Editor" which you tin can use to run into the lawmaking.

And when yous click on the avant-garde editor it will testify you the code editor and that code looks something like below:

M is a instance sensitive language and similar all the other languages it uses variables and expressions. The basic structure of lawmaking looks like below where the lawmaking starts with the Let expression.

In this code, we take ii variables and the values defined to them. In the finish, to go the value, IN expression has been used. At present when you click OK it volition render the value assigned to the variable "Variablename" in the consequence.

Check out this resource to learn more almost Ability Query Language.

In the Finish

What is Excel Power Query?

Power Query is a data transforming engine which you lot can use to go data from multiple sources, make clean and transform that information and then use it further in the analysis.

You tin can't afford to avoid the POWER QUERY. If you think similar this, a lot of things which we exercise with Excel functions or VBA codes can be automated using it, and I'1000 sure this tutorial inspires yous to apply information technology more and more.

But at present you need to tell me i affair. Which affair do you like most near the Power QUERY?

Y'all must cheque out these tutorials

• Create a Pin Table from Multiple Files

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

Posted by: barkeradicullece.blogspot.com

Share this post