教程:使用批量检测和 Power BI 将异常可视化Tutorial: Visualize anomalies using batch detection and Power BI

使用本教程查找设置为批的时序数据中的异常。Use this tutorial to find anomalies within a time series data set as a batch. 使用 Power BI Desktop 时,需获取一个 Excel 文件,为异常检测器 API 准备数据,然后将其中的统计异常可视化。Using Power BI desktop, you will take an Excel file, prepare the data for the Anomaly Detector API, and visualize statistical anomalies throughout it.

本教程介绍以下操作:In this tutorial, you'll learn how to:

  • 使用 Power BI Desktop 导入和转换时序数据集Use Power BI Desktop to import and transform a time series data set
  • 将 Power BI Desktop 与异常检测器 API 集成,以便进行批量异常检测Integrate Power BI Desktop with the Anomaly Detector API for batch anomaly detection
  • 将在数据中发现的异常可视化,其中包括预期的和看到的值,以及异常检测边界。Visualize anomalies found within your data, including expected and seen values, and anomaly detection boundaries.


  • Azure 订阅An Azure subscription
  • 免费提供的 Microsoft Power BI DesktopMicrosoft Power BI Desktop, available for free.
  • 一个 Excel 文件 (.xlsx),其中包含时序数据点。An excel file (.xlsx) containing time series data points. 可在 GitHub 上找到本快速入门的示例数据The example data for this quickstart can be found on GitHub
  • 拥有 Azure 订阅后,可在 Azure 门户中创建异常检测器资源来获取密钥和终结点。Once you have your Azure subscription, create an Anomaly Detector resource in the Azure portal to get your key and endpoint.
    • 需要从创建的资源获取密钥和终结点,以便将应用程序连接到异常检测器 API。You will need the key and endpoint from the resource you create to connect your application to the Anomaly Detector API. 稍后会在本快速入门中执行此操作。You'll do this later in the quickstart.


为了在使用异常检测器 API 时达到最佳效果,JSON 格式的时间序列数据应包括:For best results when using the Anomaly Detector API, your JSON-formatted time series data should include:

  • 以相同间隔分隔的数据点,缺少的所需点数不超过 10%。data points separated by the same interval, with no more than 10% of the expected number of points missing.
  • 如果数据不具有明确的季节性模式,则至少有 12 个数据点。at least 12 data points if your data doesn't have a clear seasonal pattern.
  • 如果数据具有明确的季节性模式,则至少有 4 个模式匹配项。at least 4 pattern occurrences if your data does have a clear seasonal pattern.

加载时序数据并设置其格式Load and format the time series data

打开 Power BI Desktop 并加载你在先决条件部分下载的时序数据即可开始。To get started, open Power BI Desktop and load the time series data you downloaded from the prerequisites. 此 Excel 文件包含一系列协调世界时 (UTC) 时间戳和值对。This excel file contains a series of Coordinated Universal Time (UTC) timestamp and value pairs.


Power BI 可以使用各种源(例如 .csv 文件、SQL 数据库、Azure Blob 存储等)提供的数据。Power BI can use data from a wide variety of sources, such as .csv files, SQL databases, Azure blob storage, and more.

在 Power BI Desktop 主窗口中,单击“开始”功能区。In the main Power BI Desktop window, click the Home ribbon. 在功能区的“外部数据”组中,打开“获取数据”下拉菜单,然后单击“Excel”。In the External data group of the ribbon, open the Get Data drop-down menu and click Excel.

Power BI 中“获取数据”按钮的图像

对话框出现以后,导航到下载了示例 .xlsx 文件的文件夹,然后将其选中。After the dialog appears, navigate to the folder where you downloaded the example .xlsx file and select it. 在“导航器”对话框出现以后,单击“Sheet1”,然后单击“编辑”。 After the Navigator dialogue appears, click Sheet1, and then Edit.

Power BI 中数据源“导航器”屏幕的图像

Power BI 会将第一列中的时间戳转换为 Date/Time 数据类型。Power BI will convert the timestamps in the first column to a Date/Time data type. 这些时间戳必须转换为文本才能发送到异常检测器 API。These timestamps must be converted to text in order to be sent to the Anomaly Detector API. 如果 Power Query 编辑器没有自动打开,请单击主页选项卡上的“编辑查询”。If the Power Query editor doesn't automatically open, click Edit Queries on the home tab.

单击 Power Query 编辑器中的“转换”功能区。Click the Transform ribbon in the Power Query Editor. 在“任何列”组中,打开“数据类型:”下拉菜单,然后选择“文本”。In the Any Column group, open the Data Type: drop-down menu, and select Text.


获得有关更改列类型的通知后,请单击“替换当前项”。When you get a notice about changing the column type, click Replace Current. 然后,在“主页”功能区中单击“关闭并应用”或“应用”。Afterwards, click Close & Apply or Apply in the Home ribbon.

创建一个函数来发送数据并设置响应的格式Create a function to send the data and format the response

若要设置数据文件的格式并将其发送到异常检测器 API,可以调用一个在上面创建的表的查询。To format and send the data file to the Anomaly Detector API, you can invoke a query on the table created above. 在 Power Query 编辑器的“开始”功能区中,打开“新建源”下拉菜单并单击“空白查询”。In the Power Query Editor, from the Home ribbon, open the New Source drop-down menu and click Blank Query.

确保选中新建查询,然后单击“高级编辑器”。Make sure your new query is selected, then click Advanced Editor.


在高级编辑器中,使用以下 Power Query M 代码片段从表中提取列并将其发送到 API。Within the Advanced Editor, use the following Power Query M snippet to extract the columns from the table and send it to the API. 然后,查询会根据 JSON 响应创建一个表并将其返回。Afterwards, the query will create a table from the JSON response, and return it. apiKey 变量替换为有效的异常检测器 API 密钥,并将 endpoint 替换为终结点。Replace the apiKey variable with your valid Anomaly Detector API key, and endpoint with your endpoint. 将查询输入高级编辑器以后,单击“完成”。After you've entered the query into the Advanced Editor, click Done.

(table as table) => let

    apikey      = "[Placeholder: Your Anomaly Detector resource access key]",
    endpoint    = "[Placeholder: Your Anomaly Detector resource endpoint]/anomalydetector/v1.0/timeseries/entire/detect",
    inputTable = Table.TransformColumnTypes(table,{{"Timestamp", type text},{"Value", type number}}),
    jsontext    = Text.FromBinary(Json.FromValue(inputTable)),
    jsonbody    = "{ ""Granularity"": ""daily"", ""Sensitivity"": 95, ""Series"": "& jsontext &" }",
    bytesbody   = Text.ToBinary(jsonbody),
    headers     = [#"Content-Type" = "application/json", #"Ocp-Apim-Subscription-Key" = apikey],
    bytesresp   = Web.Contents(endpoint, [Headers=headers, Content=bytesbody, ManualStatusHandling={400}]),
    jsonresp    = Json.Document(bytesresp),

    respTable = Table.FromColumns({

                     Table.Column(inputTable, "Timestamp")
                     ,Table.Column(inputTable, "Value")
                     , Record.Field(jsonresp, "IsAnomaly") as list
                     , Record.Field(jsonresp, "ExpectedValues") as list
                     , Record.Field(jsonresp, "UpperMargins")as list
                     , Record.Field(jsonresp, "LowerMargins") as list
                     , Record.Field(jsonresp, "IsPositiveAnomaly") as list
                     , Record.Field(jsonresp, "IsNegativeAnomaly") as list

                  }, {"Timestamp", "Value", "IsAnomaly", "ExpectedValues", "UpperMargin", "LowerMargin", "IsPositiveAnomaly", "IsNegativeAnomaly"}

    respTable1 = Table.AddColumn(respTable , "UpperMargins", (row) => row[ExpectedValues] + row[UpperMargin]),
    respTable2 = Table.AddColumn(respTable1 , "LowerMargins", (row) => row[ExpectedValues] -  row[LowerMargin]),
    respTable3 = Table.RemoveColumns(respTable2, "UpperMargin"),
    respTable4 = Table.RemoveColumns(respTable3, "LowerMargin"),

    results = Table.TransformColumnTypes(

                {{"Timestamp", type datetime}, {"Value", type number}, {"IsAnomaly", type logical}, {"IsPositiveAnomaly", type logical}, {"IsNegativeAnomaly", type logical},
                 {"ExpectedValues", type number}, {"UpperMargins", type number}, {"LowerMargins", type number}}

 in results

调用数据工作表上的查询,方法是:在“输入参数”下选择 Sheet1,然后单击“调用”。Invoke the query on your data sheet by selecting Sheet1 below Enter Parameter, and click Invoke.


数据源隐私和身份验证Data source privacy and authentication


请注意组织的数据隐私和访问政策。Be aware of your organization's policies for data privacy and access. 有关详细信息,请参阅 Power BI Desktop 隐私级别See Power BI Desktop privacy levels for more information.

在尝试运行此查询时,可能会出现警告消息,因为此查询使用外部数据源。You may get a warning message when you attempt to run the query since it utilizes an external data source.

一个显示由 Power BI 创建的警告的图像

若要修复此问题,请单击“文件”,然后单击“选项和设置”。To fix this, click File, and Options and settings. 然后单击“选项”。Then click Options. 在“当前文件”下选择“隐私”,然后选择“忽略隐私级别并潜在地改善性能”。 Below Current File, select Privacy, and Ignore the Privacy Levels and potentially improve performance.

另外,可能会出现一条消息,要求指定连接到 API 的方式。Additionally, you may get a message asking you to specify how you want to connect to the API.


若要修复此问题,请单击消息中的“编辑凭据”。To fix this, Click Edit Credentials in the message. 在对话框出现后选择“匿名”,以匿名方式连接到 API。After the dialogue box appears, select Anonymous to connect to the API anonymously. 然后单击“连接”。Then click Connect.

然后在“主页”功能区中单击“关闭并应用”,应用所做的更改。Afterwards, click Close & Apply in the Home ribbon to apply the changes.

可视化异常检测器 API 响应Visualize the Anomaly Detector API response

在 Power BI 主屏幕中,开始使用上面创建的查询将数据可视化。In the main Power BI screen, begin using the queries created above to visualize the data. 首先选择“可视化效果”中的“折线图”。 First select Line Chart in Visualizations. 然后,将已调用函数中的时间戳添加到折线图的“轴”。Then add the timestamp from the invoked function to the line chart's Axis. 右键单击它,并选择“时间戳”。Right-click on it, and select Timestamp.


将“已调用函数”中的以下字段添加到图的“值”字段。 Add the following fields from the Invoked Function to the chart's Values field. 根据以下屏幕截图来构建图表。Use the below screenshot to help build your chart.

  • ValueValue
  • UpperMarginsUpperMargins
  • LowerMarginsLowerMargins
  • ExpectedValuesExpectedValues


添加字段后,单击图表并重设其大小,以便显示所有数据点。After adding the fields, click on the chart and resize it to show all of the data points. 图表的外观应类似于以下屏幕截图:Your chart will look similar to the below screenshot:


显示异常数据点Display anomaly data points

在 Power BI 窗口右侧的“字段”窗格下面,右键单击“已调用函数查询”下的“值” ,然后单击“新建快速度量”。On the right side of the Power BI window, below the FIELDS pane, right-click on Value under the Invoked Function query, and click New quick measure.


在出现的屏幕上,选择“筛选的值”进行计算。On the screen that appears, select Filtered value as the calculation. 将“基础值”设置为Sum of ValueSet Base value to Sum of Value. 然后将 IsAnomaly 从“已调用函数”字段拖至“筛选器”。 Then drag IsAnomaly from the Invoked Function fields to Filter. 从“筛选器”下拉菜单中选择 TrueSelect True from the Filter drop-down menu.


单击“确定”后,会在字段列表底部出现一个Value for True字段。After clicking Ok, you will have a Value for True field, at the bottom of the list of your fields. 右键单击它,将其重命名为“异常”。Right-click it and rename it to Anomaly. 将其添加到图表的“值”。Add it to the chart's Values. 然后选择“格式”工具,将 X 轴类型设置为“分类”。Then select the Format tool, and set the X-axis type to Categorical.

设置 X 轴格式的图像

为图表应用颜色,方法是:单击“格式”工具和“数据颜色”。 Apply colors to your chart by clicking on the Format tool and Data colors. 图表的外观应如下所示:Your chart should look something like the following:


后续步骤Next steps