Excel Dax 简明教程

Excel DAX - Updating Data in the Data Model

DAX 用于对 Excel Power Pivot 中数据模型中的数据进行计算。DAX 使数据建模和报告活动得到有效处理。但是，这需要时不时更新数据模型中的数据，以便反映当前数据。

DAX is used for calculations on the data in the Data Model in Excel Power Pivot. DAX enables data modeling and reporting activities to be handled in an effective way. However, this requires updating the data in the Data Model from time to time so as to reflect the current data.

您可以通过建立数据连接，从外部数据源将数据导入到工作簿的数据模型中。您可以随时从源更新数据。如果您要获取来自包含实时销售信息或每天更新多次的数据源的关系数据库中的数据，那么此选项非常方便。

You can import data from an external data source into the Data Model of your workbook by establishing a data connection. You can update the data from the source whenever you choose. This option is handy if you are getting data from relational databases that contain live sales information or data feeds that are updated several times a day.

Different Ways of Updating Data in the Data Model

您可以通过以下方式更新数据模型中的数据 -

You can update the data in the Data Model in the following ways −

  1. Refreshing data in the Data Model from time to time.

  2. Making changes to data sources, such as connection properties.

  3. Updating the data in the Data Model after the source data has changed.

  4. Filtering the data to selectively load rows from a table in the data source.

Refreshing Data in the Data Model

除了从现有源获取更新的数据外，每当您对源数据的架构进行更改时，您还需刷新工作簿中的数据。这些更改可包括添加列或表，或更改导入的行。

In addition to getting updated data from an existing source, you will need to refresh data in your workbook whenever you make changes to the schema of the source data. These changes can include adding columns or tables, or changing the rows that are imported.

请注意，添加数据、更改数据或编辑筛选器始终会触发依赖于该数据源的 DAX 公式的重新计算。有关详细信息，请参阅章节 - 重新计算 DAX 公式。

Note that addition of data, changing data, or editing filters always triggers recalculation of DAX formulas that depend on that data source. Refer to the chapter – Recalculating DAX Formulas for details.

数据模型中有两种类型的数据刷新 -

You have two types of data refresh in Data Model −

Manual Refresh

如果您选择手动刷新选项，则可以随时手动刷新数据模型中的数据。您可以刷新所有数据（这是默认设置），或者可以手动选择要为各个数据源刷新的表和列。

If you choose manual refresh option, you can refresh the data in the Data Model manually at any time. You can refresh all data, which is the default, or you can manually choose the tables and columns to refresh for individual data sources.

Automatic or Scheduled Refresh

如果您已将工作簿发布到 PowerPivot 库或支持 PowerPivot 的 SharePoint 网站，那么您或 SharePoint 管理员可以创建用于自动更新工作簿中数据的计划。在这种情况下，您可以在服务器上计划无人值守的数据刷新。

If you have published your workbook to a PowerPivot Gallery or SharePoint site that supports PowerPivot, you or the SharePoint administrator can create a schedule for automatically updating the data in the workbook. In such a case, you can schedule unattended data refresh on the server.

Manually Refreshing an Existing Data Source

如果需要更新来自现有数据源的数据或获取用于设计新 DAX 公式的最新数据，你可以随时手动刷新你的数据。你可以刷新单个表格、共享相同数据连接的所有表格或数据模型中的所有表格。

You can manually refresh your data any time, if you need to update the data from an existing data source or get the recent data for designing new DAX formulas. You can refresh a single table, all tables that share the same data connection or all tables in the Data Model.

如果你从关系数据源（如 SQL Server 和 Oracle）导入了数据，你可以在一次操作中更新所有相关表格。将新数据或更新的数据加载到数据模型的操作通常会触发 DAX 公式的重新计算，而这两项操作都可能需要一些时间才能完成。因此，你应该在更改数据源或刷新从数据源获取的数据之前意识到潜在的影响。

If you have imported data from a relational data source, such as SQL Server and Oracle, you can update all the related tables in one operation. The operation of loading new or updated data into the Data Model often triggers recalculation of DAX formulas, both of which might require some time to complete. Hence, you should be aware of the potential impact before you change data sources or refresh the data that is obtained from the data source.

若要刷新数据模型中单个表格或所有表格的数据，请执行以下操作：

To refresh data for a single table or all tables in a Data Model, do the following −

  1. Click the Home tab on the Ribbon in the Power Pivot window.

  2. Click Refresh.

  3. Click Refresh in the dropdown list for refreshing the selected table.

  4. Click Refresh All in the dropdown list for refreshing all the tables.

manually resfreshing

若要刷新数据模型中使用相同连接的所有表格的数据，请执行以下操作：

To refresh data for all tables that use the same connection in a Data Model, do the following −

  1. Click the Home tab on the Ribbon in Power Pivot window.

  2. Click the Existing Connections in the Get External Data group.

出现“现有连接”对话框。

Existing Connections dialog box appears.

  1. Select a connection.

  2. Click the Refresh button.

existing connections

出现“数据刷新”对话框，并且将显示数据刷新进度信息，因为 PowerPivot 引擎正在从所选表格或数据源中的所有表格重新加载数据。

Data Refresh dialog box appears and data refresh progress information is displayed as the PowerPivot engine reloads data from the selected table or from all tables from the data source.

有三种可能的结果：

There are three possible outcomes −

  1. Success − Reports on the number of rows imported into each table.

  2. Error − An error can occur if the database is offline, you no longer have permissions. A table or column is deleted or renamed in the source.

  3. Cancelled − This means Excel did not issue the refresh request, probably because refresh is disabled on the connection.

possible outcomes

单击“关闭”按钮。

Click the Close button.

Changing a Data Source

若要更改数据模型中的数据，你可以在 Power Pivot 窗口中编辑连接信息或更新数据模型中所用表格和列的定义。

To change the data in your Data Model, you can edit the connection information or update the definition of the tables and columns used in your Data Model in the Power Pivot window.

你可以对现有数据源进行以下更改：

You can make the following changes to the existing data sources −

Connections

  1. Edit the database name or the server name.

  2. Change the name of the source text file, spreadsheet, or data feed.

  3. Change the location of the data source.

  4. For relational data sources, change the default catalog or initial catalog.

  5. Change the authentication method or the credentials used to access the data.

  6. Edit advanced properties on the data source.

Tables

  1. Add or remove a filter on the data.

  2. Change the filter criteria.

  3. Add or remove tables.

  4. Change the table names.

  5. Edit mappings between tables in the data source and tables in the Data Model.

  6. Select different columns from the data source.

Columns

  1. Change the column names.

  2. Add new columns.

  3. Delete columns from the Data Model (does not affect the data source).

可通过以下方式编辑现有数据源的属性：

You can edit the properties of an existing data source in the following ways −

  1. You can change the connection information, including the file, feed, or database used as a source, its properties or other provider specific connection options.

  2. You can change the table and column mappings and remove references to columns that are no longer used.

  3. You can change the tables, views, or columns that you get from the external data source.

Modifying a Connection to an Existing Data Source

可以通过更改当前连接所用的外部数据源，来修改到外部数据源中创建的连接。但是，需要遵循的过程取决于数据源类型。

You can modify the connection that you have created to an external data source by changing the external data source used by the current connection. However, the procedure to be followed depends on the data source type.

  1. Click the Home tab on the Ribbon in the PowerPivot window.

  2. Click the Existing Connections in the Get External Data group.

modifying connection

出现“现有连接”对话框。选择要修改的连接。

Existing Connections dialog box appears. Select the connection that you want to modify.

根据所更改数据源的类型，供应商可能不同。另外，可用的属性可能需要更改。考虑一个连接到包含数据的 Excel 工作簿的简单示例。

Depending on the type of the data source you are changing, the provider might be different. Also the properties that are available may require change. Consider a simple example of a connection to an Excel workbook that contains the data.

excel workbook contains data

  1. Click the Edit button. Edit Connection dialog box appears.

  2. Click the Browse button to locate another database of the same type (Excel workbook in this example), but with a different name or location.

  3. Click the Open button.

新文件将被选中。会出现一条消息，说明您已修改连接信息，并且需要保存并刷新表以验证连接。

The new file will get selected. A message appears stating that you have modified connection information and you need to save and refresh the tables to verify the connection.

message

  1. Click the Save button. You will be back in the Existing Connections dialog box.

  2. Click the Refresh button. Data Refresh dialog box appears displaying the data refresh progress. The status of data refresh will be displayed. Refer to the section - Manually Refreshing an Existing Data Source for details.

  3. Click Close, once the data refresh is a success.

  4. Click Close in the Existing Connections dialog box.

Editing Table and Column Mappings (Bindings)

要在数据源更改时编辑列映射，请执行以下操作：

To edit the column mappings when a data source changes, do the following −

  1. Click the tab that contains the table you want to modify in the Power Pivot window.

  2. Click the Design tab on the Ribbon.

  3. Click the Table Properties.

editing table

将出现“编辑表属性”对话框。

Edit Table Properties dialog box appears.

edit table properties

您可以观察到以下内容 −

You can observe the following −

  1. The name of the selected table in the Data Model is displayed in the Table Name box.

  2. The name of the corresponding table in the external data source is displayed in the Source Name box.

  3. There are two options for column names from – Source and Modal.

  4. If the columns are named differently in the data source and in the Data Model, you can toggle between the two sets of column names by selecting these options.

  5. Preview of the selected table appears in the dialog box.

您可以编辑以下内容：

You can edit the following −

  1. To change the table that is used as a data source, select a different table than the selected one in the Source Name dropdown list.

  2. Change the column mappings if needed − To add a column that is present in the source but not in the Data Model, select the checkbox beside the column name. Repeat for all the columns that are to be added. The actual data will be loaded into the Data Model, the next time you refresh. If some columns in the Data Model are no longer available in the current data source, a message appears in the notification area that lists the invalid columns. You do not need to do anything.

  3. Click the Save button.

当您保存当前表属性集时，您将收到一条消息 - 请稍候。然后将显示检索到的行数。

When you save the current set of table properties, you will get a message – Please wait. Then the number of rows retrieved will be displayed.

在数据模型中的表中，任何无效列将自动删除，并将添加新列。

In the table in the Data Model, any invalid columns are automatically removed and new columns are added.

Changing a Column Name and Data Type

您可以按如下方式更改数据模型中表中的列名称：

You can change the name of a column in a table in the Data Model as follows −

  1. Double-click on the header of the column. The name of the column in the header will get highlighted.

  2. Type the new column name, overwriting the old name. Alternatively, you can change the name of a column in a table in the Data Model as follows:

  3. Select the column by clicking on its header.

  4. Right-click the column.

  5. Click Rename Column in the dropdown list.

changing column name

标题中列标题的名称将被高亮显示。输入新的列名称，覆盖旧名称。

The name of the column in the header will get highlighted. Type the new column name, overwriting the old name.

正如您所了解的，数据模型中表中列中的所有值必须具有相同的数据类型。

As you have learnt, all the values in a column in a table in the Data Model must be of the same data type.

若要更改列的数据类型，请执行以下操作：

To change the data type of a column, do the following −

  1. Select the column that you want to change by clicking its header.

  2. Click the Home tab on the Ribbon.

  3. Click the controls in the Formatting group to modify the column’s data type and format.

check the controls

Adding / Changing a Filter to a Data Source

您可以向数据源添加筛选器，当您导入数据时，可以限制数据模型中表中的行数。在以后，可以通过更改您之前定义的筛选器向数据模型中的表中添加更多行，或减少行数。

You can add a filter to a data source when you import data to restrict the number of rows in the table in the Data Model. Later, you can add more rows or decrease the number of rows in the table in the Data Model by changing the filter that you defined earlier.

Adding a Filter to a Data Source During Import

若要在数据导入期间向数据源添加新筛选器，请执行以下操作：

To add a new filter to a data source during data import, do the following −

  1. Click the Home tab on the Ribbon in Power Pivot window.

  2. Click one of the data sources in the Get External Data group.

表导入向导对话框出现。

Table Import Wizard dialog box appears.

  1. Proceed to the step – Select Tables and Views.

  2. Select a table and then click Preview & Filter.

adding a filter to data source

预览选定表对话框将出现。

Preview Selected Table dialog box appears.

  1. Click the column on which you want to apply filter.

  2. Click the down arrow to the right of the column heading.

preview selected table

若要添加筛选器，请执行以下操作之一：

To add a filter, do one of the following −

  1. In the list of column values, select or clear one or more values to filter by and then click OK. However, if the number of values is extremely large, individual items might not be shown in the list. Instead, you will see the message - "Too many items to show."

  2. Click Number Filters or Text Filters (depending on the data type of the column). Then, click one of the comparison operator commands (such as Equals), or click Custom Filter. In the Custom Filter dialog box, create the filter and then click OK.

@{s0} − 如果您在任何阶段出错，请单击“清除行筛选器”按钮并重新开始。

Note − If you make a mistake at any stage, click the Clear Row Filters button and start over.

  1. Click OK. You will be back to Select Tables and Views page of Table Import Wizard.

view page

您可以看到，在列“筛选器详细信息”中，应用筛选器链接出现在您定义筛选器的列中。

As you can observe, in the column – Filter Details, a link Applied Filters appears for the column on which you defined the filter.

您可以单击链接以查看由向导建立的筛选器表达式。但是，每个筛选器表达式的语法取决于提供程序，您无法编辑它。

You can click the link to view the filter expression that was built by the wizard. But, the syntax for each filter expression depends on the provider and you cannot edit it.

link to view

  1. Click Finish to import the data with filters applied.

  2. Close the Table Import Wizard.

Changing a Filter to an Existing Data Source

在导入数据后，您可能必须定期更新它，方法是添加更多行或通过限制表中的现有行。在这种情况下，您可以更改表上的现有筛选器或添加新筛选器。

After you have imported the data, you might have to update it from time to time, by either adding more rows or by restricting the existing rows in the table. In such a case, you can change the existing filters on the table or add new filters.

  1. Click the Home tab on the Ribbon in Power Pivot window.

  2. Click the Existing Connections in the Get External Data group. Existing Connections dialog box appears.

  3. Click the connection that contains the table on which you have to change the filter.

  4. Click the Open button.

changing a filter

您将进入表导入向导对话框。重复上一部分中的步骤以筛选列。

You will get into Table Import Wizard dialog box. Repeat the steps in the previous section to filter the columns.