在查詢編輯器中使用 RUsing R in Query Editor

您可以在 Power BI Desktop 查詢編輯器中使用 R,統計學家、資料科學家和資料分析師廣泛使用的程式設計語言。You can use R, a programming language widely used by statisticians, data scientists, and data analysts, in the Power BI Desktop Query Editor. 將 R 整合到資料編輯器中,可讓您使用 R 執行資料清理,以及在資料集執行進階資料成形與分析,包括補足遺漏的資料、預測及叢集等。This integration of R into Query Editor lets you perform data cleansing using R, and perform advanced data shaping and analytics in datasets, including completion of missing data, predictions, and clustering, just to name a few. R是功能強大的語言,可用於查詢編輯器以準備您的資料模型及建立報表。R is a powerful language, and can be used in Query Editor to prepare your data model and create reports.

安裝 RInstalling R

若要在 Power BI Desktop 的使用 R,您需要在本機電腦上安裝 RTo use R in Power BI Desktop's Query Editor, you need to install R on your local machine. 您可以從許多位置免費下載並安裝 R,包括 Revolution Open 下載頁面以及 CRAN 儲存機制You can download and install R for free from many locations, including the Revolution Open download page, and the CRAN Repository.

在查詢編輯器中使用 RUsing R in Query Editor

為了示範如何在查詢編輯器中使用 R,我們會使用來自股市資料集的範例,您可以從這裡下載該 .CSV 檔案並遵循指示。To show how to use R in Query Editor, we'll use an example from a stock market dataset, based on a .CSV file that you can download from here and follow along. 本範例的步驟如下所示:The steps for this example are the following:

  1. 首先,將您的資料載入 Power BI DesktopFirst, load your data into Power BI Desktop. 在本範例中,我們載入 EuStockMarkets_NA.csv 檔案。In this example we'll load the EuStockMarkets_NA.csv file. Power BI Desktop 的 [首頁] 功能區選取 [取得資料] > [CSV]Select Get Data > CSV from the Home ribbon in Power BI Desktop.

  2. 選取檔案後選取 [開啟],CSV 隨即顯示在 [CSV 檔案] 對話方塊中。Select the file and select Open, and the CSV is displayed in the CSV file dialog.

  3. 資料載入後,會出現在 Power BI Desktop 的 [欄位] 窗格中。Once the data is loaded, you'll see it in the Fields pane in Power BI Desktop.

  4. 您也可以從 Power BI Desktop的 [首頁] 索引標籤選取 [編輯查詢],以開啟查詢編輯器Open Query Editor by selecting Edit Queries from the Home tab in Power BI Desktop.

  5. 在 [轉換] 索引標籤中選取 [執行 R 指令碼],[執行 R 指令碼] 編輯器隨即出現 (於下個步驟示範)。In the Transform tab, select Run R Script and the Run R Script editor appears (shown in the next step). 請注意,第 15 和 20 列遺失了資料,您在下圖中無法看到的其他列亦然。Notice that rows 15 and 20 suffer from missing data, as do other rows you can't see in the following image. 下方步驟示範 R 可以 (將會) 如何為您補足這些列。The steps below show how R can (and will) complete those rows for you.

  6. 對於本範例,我們會輸入下列指令碼:For this example, we'll enter the following script code:

    library(mice)
    tempData <- mice(dataset,m=1,maxit=50,meth='pmm',seed=100)
    completedData <- complete(tempData,1)
    output <- dataset
    output$completedValues <- completedData$"SMI missing values"
    

    注意

    您的 R 環境中必須安裝 mice 程式庫,前述指令碼才能正常運作。You'll need to have the mice library installed in your R environment for the previous script code to work properly. 若要安裝 mice,請在 R 安裝中執行下列命令:| > install.packages('mice')To install mice, run the following in your R installation: | > install.packages('mice')

    放入 [執行 R 指令碼] 對話方塊時,程式碼看起來會像這樣:When put into the Run R Script dialog, the code looks like the following:

  7. 選取 [確定] 時,查詢編輯器會顯示有關資料隱私權的警告。When we select OK, Query Editor displays a warning about data privacy.

  8. 為使 R 指令碼在 Power BI 服務中正常運作,所有資料來源都必須設為「公開」。For the R scripts to work properly in the Power BI service, all data sources need to be set to public. 如需隱私權設定及其含意的詳細資訊,請參閱隱私權等級For more information about privacy settings and their implications, see Privacy Levels.

    一旦這樣做,就會在名為 completedValues 的 [欄位] 區中看到新的一行。Once we do so, we see a new column in the Fields well called completedValues. 請注意,這裡有多個遺失的資料項目,像是第 15 和 18 列。Notice there are a few missing data elements, such as on row 15 and 18. 下一節說明 R 如何處理該狀況。We'll see how R handles that in the next section.

在 R 指令碼只有五行的情況下,查詢編輯器透過預測模型填入遺失的值。With just five lines of R script, Query Editor filled in the missing values with a predictive model.

從 R 指令碼資料建立視覺效果Creating visuals from R script data

現在我們可以建立視覺效果,以了解如何 R 指令碼如何使用 mice 程式庫補足遺失的值,如下圖所示。Now we can create a visual to see how the R script code using the mice library completed the missing values, as shown in the following image.

一旦完成該視覺效果以及其他要使用 Power BI Desktop建立的任何視覺效果,就可以儲存 Power BI Desktop 檔案 (儲存為 .pbix 檔案),然後在 Power BI 服務中使用資料模型,包括屬於其中的 R 指令碼。Once that visual is complete, and any other visuals we might want to create using Power BI Desktop, we can save the Power BI Desktop file (which saves as a .pbix file) and then use the data model, including the R scripts that are part of it, in the Power BI service.

注意

想查看完成這些步驟後的完成後 .pbix 檔案嗎?Want to see a completed .pbix file with these steps completed? 幸運的是,您可以在這裡下載這些範例中使用的完成後 Power BI Desktop 檔案。You're in luck - you can download the completed Power BI Desktop file used in these examples right here.

一旦將 .pbix 檔案上傳到 Power BI 服務,就必須再執行幾個步驟以 (在服務中) 啟用資料重新整理,並讓視覺效果在服務中保持更新 (資料需要 R 存取權以更新視覺效果)。Once you've uploaded the .pbix file to the Power BI service, a couple more steps are necessary to enable data refresh (in the service) and to enable visuals to be updated in the service (the data needs access to R for visuals to be updated). 其他步驟如下:The additional steps are the following:

  • 為資料集啟用排程重新整理 - 若要為包含使用 R 指令碼之資料集的活頁簿啟用排程重新整理,請參閱設定排程重新整理,其中也包括 Personal Gateway相關資訊。Enable scheduled refresh for the dataset - to enable scheduled refresh for the workbook that contains your dataset with R scripts, see Configuring scheduled refresh, which also includes information about Personal Gateway.
  • 安裝 Personal Gateway - 檔案及 R 安裝所在的電腦上必須安裝 Personal Gateway;Power BI 必須存取該活頁簿及重新轉譯任何更新的視覺效果。Install the Personal Gateway - you need a Personal Gateway installed on the machine where the file is located, and where R is installed; the Power BI service must access that workbook and re-render any updated visuals. 您可以取得如何安裝及設定 Personal Gateway 的詳細資訊。You can get more information on how to install and configure Personal Gateway.

限制Limitations

建立於查詢編輯器並含有 R 指令碼的查詢有幾項限制:There are some limitations to queries that include R scripts created in Query Editor:

  • 所有 R 資料來源設定都必須設為「公開」,建立於查詢編輯器的查詢中其他所有步驟也都必須為公開。All R data source settings must be set to Public, and all other steps in a query created in Query Editor must also be public. 若要取得資料來源設定,請在 Power BI Desktop 中選取 [檔案] > [選像和設定] > [資料來源設定]To get to data source settings, in Power BI Desktop select File > Options and settings > Data source settings.

    從 [資料來源設定] 對話方塊選取資料來源,然後選取 [編輯參數...],並確認 [隱私權等級] 已設為「公開」。From the Data Source Settings dialog, select the the data source(s) and then select Edit Permissions... and ensure that the Privacy Level is set to Public.

  • 若要啟用 R 視覺效果或資料集的排程重新整理,您必須啟用 [排程重新整理],並在裝載活頁簿與 R 安裝的電腦上安裝 Personal GatewayTo enable scheduled refresh of your R visuals or dataset, you need to enable Scheduled refresh and have a Personal Gateway installed on the computer that houses the workbook and the R installation. 如需這兩者的詳細資訊,請參閱本文前一節,其中提供了深入了解各項的連結。For more information on both, see the previous section in this article, which provides links to learn more about each.

R 和自訂查詢有各種用途,您可以用想要的呈現方式探索資料並使其成形。There are all sorts of things you can do with R and custom queries, so explore and shape your data just the way you want it to appear.