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. MS Query gives Excel users the ability to access 3rd party databases, text files, and Excel workbooks as relational data sources. With text files, you can place them all in one folder to form a database. With Excel, you define several named ranges in a single workbook, and then use the ranges as database tables. To make the connection from Power Query Online: Select the Excel option in the connector selection. In the Excel dialog box that appears, provide the path to the Excel workbook. If necessary, select an on-premises data gateway to access the Excel workbook. Power Query lets you analyze your data right from within Excel, while seamlessly connecting to a wide range of external data sources. Easily reshape and transform your data along the way if needed. Get to know “Get and Transform Data” To use Power Query, just click the Data tab in Excel 2016 or newer, called “Get and Transform Data”. Using an OLE DB Query, entering a query that uses a question mark (?) as a parameter on the Command text shows an error indicating you need to set the parameter.
The CData ODBC driver for Microsoft Teams uses the standard ODBC interface to link Microsoft Teams data with applications like Microsoft Access and Excel. Follow the steps below to use Microsoft Query to import Microsoft Teams data into a spreadsheet and provide values to a parameterized query from cells in a spreadsheet.
If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.
You can connect to MS Teams using the embedded OAuth connectivity. When you connect, the MS Teams OAuth endpoint opens in your browser. Log in and grant permissions to complete the OAuth process. See the OAuth section in the online Help documentation for more information on other OAuth authentication flows.
You can then work with live Microsoft Teams data in Excel.
- In Excel, open the Data tab and choose From Other Sources -> From Microsoft Query.
- Choose the MSTeams 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. Select the columns you want to import and click the arrow to add them to your query. Alternatively, select the table name to add all columns for that table.
- The Filter Data page allows you to specify criteria. For example, you can limit results by setting a date range.
- If you want to use parameters in your query, select the option to edit the query in Microsoft Query.
To set a parameter in the query, you will need to modify the SQL statement directly. To do this, click the SQL button in the Query Editor. If you set filter criteria earlier, you should have a WHERE clause already in the query.
To use a parameter, use a '?' character as the wildcard character for a field's value in the WHERE clause. For example, if you are importing the Teams, you can set 'Id=?'.
- Close the SQL dialog when you are finished editing the SQL statement. You will be prompted to enter a parameter value. In the next step, you will select a cell to provide this value. So, leave the box in the dialog blank.
Close Microsoft Query. The Import Data dialog is displayed. Enter a cell where results should be imported.
- Close the Import Data dialog. You will be prompted to enter a parameter value. Click the button next to the parameter box to select a cell. Select the option to automatically refresh the spreadsheet when the value changes.
Summary
| Item | Description |
|---|---|
| Release State | General Availability |
| Products | Power BI (Datasets) Power BI (Dataflows) Power Apps (Dataflows) Excel Dynamics 365 Customer Insights Analysis Services |
| Authentication Types Supported | Anonymous (online) Basic (online) Organizational account (online) |
| Function Reference Documentation | Excel.Workbook Excel.CurrentWorkbook |
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: Excel viewer for mac os.
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:
Select the Excel option in the connector selection.
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.
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. Softwares for mac pro.
Connect to an Excel workbook from Power Query Online
To make the connection from Power Query Online:
Select the Excel option in the connector selection.
In the Excel dialog box that appears, provide the path to the Excel workbook.
If necessary, select an on-premises data gateway to access the Excel workbook.
If this is the first time you've accessed this Excel workbook, select the authentication kind and sign in to your account (if needed).
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:
- Open the document in Excel Desktop.
- Open the File menu, select the Info tab, and then select Copy Path.
- 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
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).
Microsoft Query In Excel
How to diagnose incorrect dimensions
To view the dimensions of a Worksheet:
- Rename the xlsx file with a .zip extension.
- Open the file in File Explorer.
- Navigate into xlworksheets.
- Copy the xml file for the problematic sheet (for example, Sheet1.xml) out of the zip file to another location.
- 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.
- 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.Workbooknow supports anInferSheetDimensionsoption. 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
Power Query Excel 365
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.
Using Microsoft Query In Excel
To fix this issue, you can refer to Locate and reset the last cell on a worksheet for detailed instructions.