Will Excel Will Allow You To Get Partial Data From An External Database?

25 Amazing Ability Query Tips and Tricks

Ability query is amazing data transformation tool! It allows you to import and transform data with ease and helps to create repeatable and robust procedures for your information.

Information technology's going to salvage you lot time and effort if you put in the small corporeality of time to learn it.

The best part is, information technology'due south built right into Excel 2016 or later. It'due south also the same technology that's used in Power BI, so you lot're learning two in demand data skills at the aforementioned time!

If you haven't heard of power query and the awesome things information technology tin do, or you want to fully understand it meliorate, then check out my Consummate Guide to Ability Query beforehand.

Here are some tips and tricks to help you get the most out of this incredibly useful and like shooting fish in a barrel tool.

Video Tutorial

Delete Steps Until Finish

Sometimes when yous are edifice a query with a lot of steps, yous end up going down the incorrect path. If you're able to pin point at which step your query started going wrong, then you tin delete that step and all steps after to get-go over (without fully starting over).

In the Applied Steps window pane, right click on the first stride you want to delete and so select Delete Until Stop from the menu.

This will delete that step and all query steps subsequently that step.

Preview or Navigate to Table Objects

A cavalcade in your query might contain table objects. If this is the case, the column'due south row entries will display the text Tabular array and the information type icon.

You lot can either preview the table or navigate to it in the query depending on where y'all click in the cell.

If you click on the bare area of a prison cell containing a tabular array object, then y'all will be shown a preview of the tabular array below your query.

When you hover the mouse cursor over the give-and-take Table in the cell, it volition change to a pointing manus icon. When you click on this part the query, it volition add a navigation step to the query which navigates to that item table.

Double Click to Edit a Query

Editing a query tin can be done a couple unlike ways from the Queries & Connections window pane.

You can right click on the query then choose Edit from the carte du jour.
You tin can hover the cursor over the query until the peek window appears, then press the Edit push from the peek window.

The quickest way is to double left click on the query. This will open up the query editor on the selected query.

Automatically Add a Row Alphabetize to any Query Loaded to a Table

You can add an index column to any query through the power query editor, merely it is too possible to add an index row to a query that's been loaded to a table in a special way. Select a cell in the table output and go to the Data tab and press the Backdrop button.

Annotation that the Properties choice found in the right click carte du jour of the Queries & Connectedness pane will open the Query Backdrop window and not the External Information Properties window that is needed for this tip.

This will open the External Data Properties menu and y'all tin bank check the Include row numbers selection and press the Ok button.

At present the side by side time you refresh the query loaded to your tabular array, a new column chosen _RowNum volition announced as the left virtually column and will contain an index for the row number starting at 0.

Alter the Default Load Options

When you press the Close & Load button for a query the get-go fourth dimension, ability query volition load the data into an Excel table in the workbook and doesn't add the data into the information moedel. This is the default load settings in ability query.

If you desire load the information as a connectedness but or need to load information technology into the information model to employ with power pivot after on, then you need to select Close & Load To instead and select these options each time y'all create a new query.

If you find most of the queries yous end up creating demand to be a connection just or need to load into the data model, then you lot tin can alter the default load settings. Go to the Data tab then choose Get Data then open the Query Options.

In the Global Data Load settings, choose the Specify custom default load settings option and then select or deselect the desired options.

Unchecking both the Load to worksheet box and the Load to Data Model box will result in your queries loading every bit connection only and will not load the data into the information model.
Checking the Load to worksheet box and unchecking the Load to Data Model box volition load queries into an Excel table and not load data to the data model. Note this is the same every bit when Use standard load settings is selected.
Unchecking the Load to worksheet box and checking the Load to Data Model box will result in your queries loading as connexion only and will load the data into the data model.
Checking both the Load to worksheet box and the Load to Data Model box volition load queries into an Excel table and will load the data into the information model.

Brandish a Monospaced Font in the Query Editor

It can sometimes exist hard to tell if each item in a cavalcade of data has the same graphic symbol length. Not all characters have the same width when using the default query editor font. This tin can brand it hard to tell if yous will be able to split a field by character length consistently for the entire column.

Change the font to a monospaced character set by going to the View tab and checking the Monospaced selection. This will make it like shooting fish in a barrel to see if things line up in character count.

Navigate Columns with the Arrow Keys

You can select a column by clicking on its column heading. The column will turn to a light light-green colour when selected and you will and so be able to perform diverse transformations on information technology past either right clicking on the column heading or using any of the transformation commands from the ribbon.

In one case a column is selected, y'all can navigate to other columns using the left or right arrow keys. In fact, y'all don't even need to select a column starting time, simply press the right arrow cardinal and the starting time column will be selected.

The aforementioned play a joke on can exist done with rows of data. Once a row is selected from clicking on the row heading yous tin can navigate to other rows using the up or downwardly arrow keys. Once more, no need to select a row first, just press the down arrow key and the first row will exist selected.

If you select a single cell in a column, you tin can utilise the Ctrl + Infinite keyboard shortcut to select the entire column. You lot can also utilise the Ctrl + A keyboard shortcut to select the unabridged table. Merely like in a regular Excel worksheet!

Comments in Power Query Formula Language

You tin can do a lot with power query just from the UI without ever touching whatsoever of the M lawmaking that is created behind the scenes. If yous do start exploring the M code using the advanced editor (View tab then Avant-garde Editor), so yous're likely going to desire to add comments to your code.

You tin can do this in ii ways.

You can create a single line comment by starting the line with a // double forward slash.

You lot can create multiple line comments by starting the first line of the comment with a /* forward slash followed by an astrix, then catastrophe the terminal line of the comment with a */ astrix followed by a forward slash.

Unfortunately, any comments you add into the advanced editor will just be visible in the avant-garde editor. You won't be able to come across them in the formula bar when navigating through the applied steps of your query.

Add Comments Without Opening the Advanced Editor

It is possible to add together comments without opening the advanced editor. Select the step from the applied steps to which you desire to add together a annotate. Add together your annotate to the finish of the step using either comment type and press the Enter button.

The comment will appear visible in the formula bar until you navigate away to a unlike stride or different query. When you come dorsum information technology won't exist visible in the formula bar any more, but it will nonetheless be there in the Grand code and visible in the advanced editor.

Add Comments that Stay Visible in the Formula Bar

I already said comments are only visible from the avant-garde editor, but at that place is a hack to get them to show upwards in the formula bar.

Write your comment inside a formula instead of at the end of the step. You'll need to use the opening /* and closing */ annotate characters as the annotate will demand to be closed and then the rest of the formula is non considered as part of the annotate.

This will remain visible in the formula bar when navigating through the practical steps of your query.

Add together Comments with the Query Step Backdrop

You can add comments to any of the query steps listed in the applied steps window pane using the query's properties window. Right click on the query and select Properties from the bill of fare.

Yous'll exist able to add a clarification to the step. This description will appear every bit a single line comment above the Thou code for the footstep in the advanced editor.

You can also change the name of the step from this window. This will change the references for the pace in the advanced editor every bit well.

Create a Sequential List

Creating a sequential list object in power query is easy when you know how. Add together a custom cavalcade with the following formula for a list starting at 5 and ending at 10.

={five..ten}

This volition fifty-fifty work by referencing columns instead of static hardcoded numbers. Employ a formula similar to the post-obit where [Start] and [Cease] are two whole number columns in your query.

={[Offset]..[Cease]}

If either the Beginning or Terminate column is a decimal number, the expression volition result in a error.

If the Start number is greater than the Stop number, the expression will consequence in an empty list.

Avoid Instance Sensitivity with Filters

Power query is example sensitive. This tin cause errors in your information analysis down the route if you're not careful.

If you lot filter out the item Keyboard from your data and later it shows up as keyboard in the source data, then your query won't filter out the lower case version.

This tin be avoided by transforming a column to upper instance earlier applying any filters.

We can likewise retain the original case in our data by using the UPPERCASE transformation establish in the Add together Column tab. This will create a new cavalcade with the upper case values while keeping the original column intact.

Now we can utilize any filter on the uppercase version of the cavalcade and and then delete the cavalcade after. This allows usa to apply a case insensitive filter without altering the instance in our data.

Double Click to Rename a Column

You're probably going to be renaming a lot of columns during your power query career so you're going to want to acquire the quickest way to practice information technology. A double left click on the cavalcade heading will allow yous to quickly change a column's proper name.

Rename a Column with a Keyboard Shortcut

You can rename a column using a keyboard shortcut. This one is a bit slower, but worth knowing for those that prefer the keyboard to the mouse. With the column selected, press F2 on the keyboard and you can rename the cavalcade heading.

Navigate Through Complex Query Dependencies

This feature tin be a life saver when your workbook starts to get overloaded with queries. It can be like shooting fish in a barrel to forget what query is using what data source and which queries depend on each other.

Go to the View tab and press the Query Dependencies command to open the query dependencies viewer. This will give y'all a visual representation of all the queries in the workbook along with their precedents and dependents.

In that location's a lot going on in the Query Dependencies window.

You'll be able to see your data sources. These are the smaller boxes in the window and volition have a larger icon indicating the type of source data.
Left click on any item and it will turn a light dark-green along with all its dependents and precedents.
Correct click on any item and a bill of fare will announced with the selection to center the view on that item or center the view on all the precedent and dependent items.
Left click and drag any empty area to move the view around.
You tin change the layout to show dependencies from top to bottom, lesser to top, left to right or right to left.
You can zoom in or out on the view manually using the zoom bar. Another way you tin can zoom in on the view is using the mouse scroll cycle if yours is equipped with one. Another pick is to double left click on any empty surface area to zoom in.
You tin fully zoom out on the view using the zoom out push button on the lower right.

Drill Down to a Single Value

Drill downwardly to any single item in your query. Right click on the cell and choose Drill Down.

Depending on your data, this could be a unmarried value, list or a table. This can be useful for creating a single value from your data which you can reference in a custom column calculation.

Enable Fast Information Load in Ability Query

Faster is always meliorate, right? If your queries are slow, you tin can enable the fast data load option to speed them up. Get to the Data tab and press the Get Data button, so open the Query Options.

Go to the Global Information Load settings and bank check the Fast Data Load box. Your queries will take less time to load, only Excel may get unresponsive during the loading.

Disable the Car Detect Data Blazon Feature

When you import data into Excel with ability query, Excel will guess what data type each column is based on the first few hundred rows. Power query volition then automatically create query steps to promote the commencement row to column headings (for CSV files) and change information types.

If you prefer to control the data blazon pick and non accept excel automatically create this steps for you, then yous can disable this selection.

To open the query options, go to the Data tab then Become Information then choose the Query Options.

Go to the Electric current Workbook Data Load settings and bank check the box to Automatically observe column types and headers for unstructured sources.

Next fourth dimension y'all create a query, you won't run into whatever steps other than the source pace.

Explore Ability Query's Function Library

Power query'southward Chiliad lawmaking linguistic communication is a functional language. This means the M code is by and large congenital with functions that exercise specific things based on their inputs. Writing G code in power query is like edifice with Lego, in that location are many dissimilar types of Lego pieces and each piece is designed for a specific purpose.

Yard code comes with a big selections of functions to use. In fact you tin explore the entire library of bachelor functions from inside the power query editor.

Create a new bare query, go to the Data tab and press the Become Data control and then choose From Other Sources and so choose Blank Query. Now in the formula bar enter =#shared and printing Enter.

Power query will then prove all the items available to the workbook. This will include all the power query functions!

If you click on any of the functions listed, y'all'll be taken to a mini assistance guide for that function.

The part proper name is shown.
There is a brief clarification of what the part does.
You lot tin test out the part on your data.
The functions syntax is shown.
There is a elementary example shown to demonstrate the input and output of the function.

Split a Query into Ii Parts

Maybe your query is getting as well long, or possibly you want to use the offset part of it as a source in other queries. If this is the case, you can separate upward a query into two parts.

Correct click on the step on which yous want to carve up the query then cull Extract Previous from the menu. This will extract the steps before the selected step into a new query. Yous'll and so be prompted to proper name the new query.

The remaining steps volition then reference the new query equally its source.

Copy and Paste Queries to a New Workbook

If you need to reuse a ability query from a previous workbook, you can hands copy and paste it from the old workbook to the new workbook.

In the Queries & Connections window of the old workbook, select the queries you want to copy so correct click and choose Copy from the carte. Now navigate to the new workbook and right click in the Queries & Connections window and choose Paste from the menu.

This will re-create all the selected queries along with all the queries they reference.

If any of the copied queries, or the queries they depend on, reference a tabular array in the old workbook and so those queries volition show a Download did not complete error message. You volition need to copy over those tables into the new workbook separately to fix this.

Double Click to Rename a Query

Y'all can chop-chop rename any query from the query listing pane on the left hand side of the query editor. Double left click on the query which you lot want to rename, then type in the new name and press Enter to ostend the change.

Double Click to Hide or Show the Query Editor Ribbon

If you find the ribbon in the power query editor is getting in the way, you lot can hibernate. Double left click on any of the ribbon tabs to hide the ribbon. Double left click on the tab again to show the ribbon.

Yeah, this is the same trick from the regular workbook ribbon but it can also be used in the power query editor!

Disable Relationship Detection

This is some other tip that can potentially speed up your power query queries.

When you load data into the information model from ability query, Excel will endeavour to find and build relationships between the new data and other tables in the information model. This extra processing step will wearisome down your queries.

If you lot are always loading your data into the data model and then you might want to turn this feature off to relieve fourth dimension. Go to the Data tab then press the Become Data button and open the Query Options.

In the Current Workbook Data Load settings, uncheck the Create relationships between tables when adding to the Data Model for the first time option. This volition forbid Excel from creating the relationships between tables in the information model.

Well-nigh the Author

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Ability Automate, Power Apps and SharePoint. You can find other interesting articles from John on his weblog or YouTube aqueduct.

Source: https://www.howtoexcel.org/power-query-tips-and-tricks/

Posted by: aldrichtrepen.blogspot.com