Note If you want the other way to create parameter queries, see Create a parameter query Power Query. Follow the Query Wizard steps. The Microsoft Query window opens and displays your query. For a generic parameter prompt, type a question mark? No helpful phrase is displayed in the prompt that appears when the query is run. For a parameter prompt that helps people provide valid input, type a phrase enclosed in square brackets.
The phrase displays in the parameter prompt when the query is run. Excel prompts you to provide a value for each parameter, then Microsoft Query displays the results. When you are ready to load the data, close the Microsoft Query window to return the results to Excel. The Import Data dialog box opens. To review your parameters, click Properties.
Then in the Connection Properties dialog box, on the Definition tab click Parameters. The Parameters dialog box displays the parameters used in the query. Select a parameter under Parameter name to review or change How parameter value is obtained. You can change the parameter prompt, enter a specific value, or specify a cell reference.
Microsoft Office provides drivers that you can use to retrieve data from the following data sources:. You can use also ODBC drivers or data source drivers from other manufacturers to retrieve information from data sources that are not listed here, including other types of OLAP databases.
For information about installing an ODBC driver or data source driver that is not listed here, check the documentation for the database, or contact your database vendor. Selecting data from a database You retrieve data from a database by creating a query, which is a question that you ask about data stored in an external database. For example, if your data is stored in an Access database, you might want to know the sales figures for a specific product by region.
You can retrieve a part of the data by selecting only the data for the product and region that you want to analyze.
With Microsoft Query, you can select the columns of data that you want and import only that data into Excel. Updating your worksheet in one operation Once you have external data in an Excel workbook, whenever your database changes, you can refresh the data to update your analysis — without having to re-create your summary reports and charts.
For example, you can create a monthly sales summary and refresh it every month when the new sales figures come in. How Microsoft Query uses data sources After you set up a data source for a particular database, you can use it whenever you want to create a query to select and retrieve data from that database — without having to retype all of the connection information.
Microsoft Query uses the data source to connect to the external database and to show you what data is available.
After you create your query and return the data to Excel, Microsoft Query provides the Excel workbook with both the query and data source information so that you can reconnect to the database when you want to refresh the data. Using Microsoft Query to import data to import external data into Excel with Microsoft Query, follow these basic steps, each of which is described in more detail in the following sections.
What is a data source? A data source is a stored set of information that allows Excel and Microsoft Query to connect to an external database. When you use Microsoft Query to set up a data source, you give the data source a name, and then supply the name and the location of the database or server, the type of database, and your logon and password information.
The information also includes the name of an OBDC driver or a data source driver, which is a program that makes connections to a specific type of database. To specify a data source for a database, text file, or Excel workbook, click the Databases tab. This tab is available only if you ran Microsoft Query from Excel. If the external database that you want to access is not supported by the ODBC drivers that are installed with Microsoft Query, then you need to obtain and install a Microsoft Office-compatible ODBC driver from a third-party vendor, such as the manufacturer of the database.
Contact the database vendor for installation instructions. To connect to other OLAP databases, you need to install a data source driver and client software.
Click Connect , and then provide the information that is needed to connect to your data source. For databases, Excel workbooks, and text files, the information that you provide depends on the type of data source that you selected. You may be asked to supply a logon name, a password, the version of the database that you are using, the database location, or other information specific to the type of database.
Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh! Weak password: House Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.
If your database has tables and you want a particular table to display automatically in the Query Wizard, click the box for step 4, and then click the table that you want. If you don't want to type your logon name and password when you use the data source, select the Save my user ID and password in the data source definition check box. The saved password is not encrypted. If the check box is unavailable, see your database administrator to determine whether this option can be made available.
Security Note: Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source. Data prep done smart, fast, easy Power Query is the easiest way to connect, extract, transform and load data from a wide range of sources. Get started.
What's new. Nov 15 Nov 15 Nov 8 Oct What is Power Query? Connect to hundreds of data sources. Search connectors. Accessible, powerful, smart data preparation. No coding needed. Everything becomes more powerful.
More proof of the power Reuse your Power Query work Create reusable data connectivity and preparation logic—even create parameters—and make adjustments to the logic across projects, all without any coding.
Learn it once, leverage across many products Power Query works across several Microsoft products, so whatever you learn for Power Query in Excel can be applied to Power BI and other products. Custom connectors Easily extend Power Query by creating your own connectors and data transformations if you need to, that can also easily be shared and used by others.
0コメント