Microsoft Query In Excel

You can then work with live QuickBooks data in Excel. In Excel, open the Data tab and choose From Other Sources - From Microsoft Query. Choose the QuickBooks DSN. Select the option to use Query Wizard to create/edit queries. In the Query Wizard, expand the node for the table you would like to import into your spreadsheet. About Power Query in Excel Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 With Power Query (called Get & Transform Data in previous Excel versions), you can import or connect to external data, and then shape that data, for example remove a column, change a data type, or merge tables, in ways that meet your needs. With Microsoft Query, you can select the columns of data that you want and import only that data into Excel. On the Data tab, in the Get & Transform Data group, click Get Data. The Microsoft Query Wizard is an easier way to quickly create a Microsoft Query right from Excel to easy access Acctivate QuickBooks Inventory Management data. The basic steps to setup a Microsoft Query using the Query Wizard are below, these may vary slightly depending on your version of Microsoft Excel. Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Power Query offers several ways to create and load Power queries into your workbook. You can also set default query load settings in the Query Options window.

Summary

Note

Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.

Prerequisites

To connect to a legacy workbook (such as .xls or .xlsb), the Access Database Engine OLEDB (or ACE) provider is required. To install this provider, go to the download page and install the relevant (32 bit or 64 bit) version. If you don't have it installed, you'll see the following error when connecting to legacy workbooks:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE can't be installed in cloud service environments. So if you're seeing this error in a cloud host (such as Power Query Online), you'll need to use a gateway that has ACE installed to connect to the legacy Excel files.

Capabilities Supported

• Import

Connect to an Excel workbook from Power Query Desktop

To make the connection from Power Query Desktop:

1. Select the Excel option in the connector selection.

2. Browse for and select the Excel workbook you want to load. Then select Open.

   If the Excel workbook is online, use the Web connector to connect to the workbook.

3. In Navigator, select the workbook information you want, then either select Load to load the data or Transform Data to continue transforming the data in Power Query Editor.

Connect to an Excel workbook from Power Query Online

To make the connection from Power Query Online:

1. Select the Excel option in the connector selection.

2. In the Excel dialog box that appears, provide the path to the Excel workbook.

3. If necessary, select an on-premises data gateway to access the Excel workbook.

4. If this is the first time you've accessed this Excel workbook, select the authentication kind and sign in to your account (if needed).

5. In Navigator, select the workbook information you want, and then Transform Data to continue transforming the data in Power Query Editor.

Troubleshooting

Connecting to an online Excel workbook

If you want to connect to an Excel document hosted in Sharepoint, you can do so via the Web connector in Power BI Desktop, Excel, and Dataflows, and also with the Excel connector in Dataflows. To get the link to the file:

1. Open the document in Excel Desktop.
2. Open the File menu, select the Info tab, and then select Copy Path.
3. Copy the address into the File Path or URL field, and remove the ?web=1 from the end of the address.

Legacy ACE connector

Power Query reads legacy workbooks (such as .xls or .xlsb) use the Access Database Engine (or ACE) OLEDB provider. Because of this, you may come across unexpected behaviors when importing legacy workbooks that don't occur when importing OpenXML workbooks (such as .xlsx). Here are some common examples.

Unexpected value formatting

Because of ACE, values from a legacy Excel workbook might be imported with less precision or fidelity than you expect. For example, imagine your Excel file contains the number 1024.231, which you've formatted for display as '1,024.23'. When imported into Power Query, this value is represented as the text value '1,024.23' instead of as the underlying full-fidelity number (1024.231). This is because, in this case, ACE doesn't surface the underlying value to Power Query, but only the value as it's displayed in Excel.

Unexpected null values

When ACE loads a sheet, it looks at the first eight rows to determine the data types of the columns. If the first eight rows aren't representative of the later rows, ACE may apply an incorrect type to that column and return nulls for any value that doesn't match the type. For example, if a column contains numbers in the first eight rows (such as 1000, 1001, and so on) but has non-numerical data in later rows (such as '100Y' and '100Z'), ACE concludes that the column contains numbers, and any non-numeric values are returned as null.

Inconsistent value formatting

Microsoft Power Query

In some cases, ACE returns completely different results across refreshes. Using the example described in the formatting section, you might suddenly see the value 1024.231 instead of '1,024.23'. This difference can be caused by having the legacy workbook open in Excel while importing it into Power Query. To resolve this problem, close the workbook.

Missing or incomplete Excel data

Sometimes Power Query fails to extract all the data from an Excel Worksheet. This failure is often caused by the Worksheet having incorrect dimensions (for example, having dimensions of A1:C200 when the actual data occupies more than three columns or 200 rows).

How to diagnose incorrect dimensions

To view the dimensions of a Worksheet:

1. Rename the xlsx file with a .zip extension.
2. Open the file in File Explorer.
3. Navigate into xlworksheets.
4. Copy the xml file for the problematic sheet (for example, Sheet1.xml) out of the zip file to another location.
5. Inspect the first few lines of the file. If the file is small enough, open it in a text editor. If the file is too large to be opened in a text editor, run the following command from a Command Prompt: more Sheet1.xml.
6. Look for a <dimension .../> tag (for example, <dimension ref='A1:C200' />).

If your file has a dimension attribute that points to a single cell (such as <dimension ref='A1' />), Power Query uses this attribute to find the starting row and column of the data on the sheet.

However, if your file has a dimension attribute that points to multiple cells (such as <dimension ref='A1:AJ45000'/>), Power Query uses this range to find the starting row and column as well as the ending row and column. If this range doesn't contain all the data on the sheet, some of the data won't be loaded.

How to fix incorrect dimensions

You can fix issues caused by incorrect dimensions by doing one of the following actions:

• Open and resave the document in Excel. This action will overwrite the incorrect dimensions stored in the file with the correct value.

• Ensure the tool that generated the Excel file is fixed to output the dimensions correctly.

• Update your M query to ignore the incorrect dimensions. As of the December 2020 release of Power Query, Excel.Workbook now supports an InferSheetDimensions option. When true, this option will cause the function to ignore the dimensions stored in the Workbook and instead determine them by inspecting the data.

  Here's an example of how to provide this option:

  Excel.Workbook(File.Contents('C:MyExcelFile.xlsx'), [DelayTypes = true, InferSheetDimensions = true])

Sluggish or slow performance when loading Excel data

Slow loading of Excel data can also be caused by incorrect dimensions. However, in this case, the slowness is caused by the dimensions being much larger than they need to be, rather than being too small. Overly large dimensions will cause Power Query to read a much larger amount of data from the Workbook than is actually needed.

To fix this issue, you can refer to Locate and reset the last cell on a worksheet for detailed instructions.

You can use Microsoft Query in Excel to retrieve data from an Excel Workbook as well as External Data Sources using SQL SELECT Statements. Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel.

Microsoft Query allows you use SQL directly in Microsoft Excel, treating Sheets as tables against which you can run Select statements with JOINs, UNIONs and more. Often Microsoft Query statements will be more efficient than Excel formulas or a VBA Macro. A Microsoft Query (aka MS Query, aka Excel Query) is in fact an SQL SELECT Statement. Excel as well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users!

Microsoft Query In Excel

What can I do with MS Query?

Using MS Query in Excel you can extract data from various sources such as:

• Excel Files – you can extract data from External Excel files as well as run a SELECT query on your current Workbook
• Access – you can extract data from Access Database files
• MS SQL Server – you can extract data from Microsoft SQL Server Tables
• CSV and Text – you can upload CSV or tabular Text files

Step by Step – Microsoft Query in Excel

In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.

I will extract data from an External Excel file called MOCK DATA.xlsx. In this file I have a list of Male/Female mock-up customers. I will want to create a simple query to calculate how many are Male and how many Female.

Open the MS Query (from Other Sources) wizard

Go to the DATA Ribbon Tab and click From Other Sources. Select the last option From Microsoft Query.

Select the Data Source

Next we need to specify the Data Source for our Microsoft Query. Select Excel Files to proceed.

Select Excel Source File

Now we need to select the Excel file that will be the source for our Microsoft Query. In my example I will select my current Workbook, the same from which I am creating my MS Query.

Select Columns for your MS Query

The Wizard now asks you to select Columns for your MS Query. If you plan to modify the MS Query manually later simply click OK. Otherwise select your Columns.

Return Query or Edit Query

Now you have two options:

1. Return Data to Microsoft Excel – this will return your query results to Excel and complete the Wizard
2. View data or edit query in Microsoft Query – this will open the Microsoft Query window and allow you to modify you Microsoft Query

Download Power Query Excel 2016

Optional: Edit Query

If you select the View data or edit query in Microsoft Query option you can now open the SQL Edit Query window by hitting the SQL button. When you are done hit the return button (the one with the open door).

Import Data

When you are done modifying your SQL statement (as I in previous step). Click the Return data button in the Microsoft Query window.
This should open the Import Data window which allows you to select when the data is to be dumped.
Lastly, when you are done click OK on the Import Data window to complete running the query. You should see the result of the query as a new Excel table:
As in the window above I have calculated how many of the records in the original table where Male and how many Female.

AS you can see there are quite a lot of steps needed to achieve something potentially pretty simple. Hence there are a couple of alternatives thanks to the power of VBA Macro….

Power Query Excel 365

MS Query – Create with VBA

If you don’t want to use the SQL AddIn another way is to create these queries using a VBA Macro. Below is a quick macro that will allow you write your query in a simple VBA InputBox at the selected range in your worksheet.
Just use my VBA Code Snippet:

Just create a New VBA Module and paste the code above. You can run it hitting the CTRL+SHIFT+S Keyboardshortcut or Add the Macro to your Quick Access Toolbar.

Learning SQL with Excel

Creating MS Queries is one thing, but you need to have a pretty good grasp of the SQL language to be able to use it’s true potential. I recommend using a simple Excel database (like Northwind) and practicing various queries with JOINs.

Alternatives in Excel – Power Query

Another way to run queries is to use Microsoft Power Query (also known in Excel 2016 and up as Get and Transform). The AddIn provided by Microsoft does require knowledge of the SQL Language, rather allowing you to click your way through the data you want to tranform.

MS Query vs Power Query Conclusions

MS Query Pros:Power Query is an awesome tool, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:

• Microsoft Queries are more efficient when you know SQL. While you can click your way through to Transform Data via Power Query someone who knows SQL will likely be much quicker in writing a suitable SELECT query
• You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query

MS Query Cons:Microsoft Query falls short of the Power Query AddIn in some other aspects however:

• Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s
• Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations

In short I encourage learning Power Query if you don’t feel comfortable around SQL. If you are advanced in SQL I think you will find using good ole Microsoft Queries more convenient. I would compare this to the Age-Old discussion between Command Line devs vs GUI devs…