取得資料Getting data

資料是 Power BI 的核心。Data is at the heart of Power BI. 在本引導式學習小節中,您會了解如何取得及使用 Power BI 中的資料。In this Guided Learning section, you learn how to get and work with data in Power BI.

您將學到如何...

Power BI Desktop 概觀Overview of Power BI Desktop

歡迎來到 Power BI 引導式學習課程的第二節<取得資料>。Welcome to the second section in this Guided Learning course for Power BI, called Getting Data. 本節探討許多 Power BI 以資料為中心的功能和工具,焦點放在 Power BI Desktop。This section looks at the many data-centric features and tools of Power BI, focusing on Power BI Desktop. 這些工具很多也適用於 Power BI 服務,所以本節的內容有雙重功用。Many of these tools also apply to the Power BI service, so you're doing double duty with your learning in this section.

當您取得資料時,有時候格式非如所想或未經 清理When you get data, sometimes it's not quite as well-formed, or clean, as you want it to be. 所以在本節中,您要學習如何取得資料、如何清理資料 (有時稱為 清除轉換 資料),以及學習一些進階技巧,讓您更容易取得資料。So in this section you learn how to get data, how to clean it up (sometimes called cleaning or transforming data), and also learn some advanced tricks that can make your data-getting life easier.

本課程始終堅持學習之旅要和 Power BI 的工作流程一致。As always in this course, your learning journey follows the same path as the flow of work in Power BI. 所以,讓我們看看常見的起點 Power BI DesktopAs such, let's check out Power BI Desktop, where it often begins.

Power BI Desktop 概觀An overview of Power BI Desktop

Power BI Desktop 是連接、清理及視覺化資料的工具。Power BI Desktop is a tool to connect to, clean, and visualize your data. 使用 Power BI Desktop,您可以連接到資料,然後用不同的方式建立模型和視覺效果。With Power BI Desktop, you can connect to data and then model and visualize it in different ways. 大部分使用商業智慧專案的使用者,會長時間使用 Power BI Desktop。Most users who are working on Business Intelligence projects will spend the majority of their time using Power BI Desktop.

您可以從 web 下載 Power BI Desktop,也可以從 Azure 市集安裝 Power BI Desktop 作為應用程式,或者可以從 Power BI 服務下載它。You can download Power BI Desktop from the web, you can also install Power BI Desktop as an app from the Windows Store, or you can download it from the Power BI service. 如需在服務中取得 Power BI Desktop,只要選取 Power BI 右上角的向下箭號按鈕,然後選取 [Power BI Desktop]。In the service, to get Power BI Desktop you just select the down arrow button in the upper right side of Power BI, then select Power BI Desktop.

Power BI Desktop 即安裝為 Windows 電腦上的應用程式。Power BI Desktop installs as an application on your Windows computer.

所以只要下載 Power BI Desktop,就可以在 Windows 上安裝並執行,和其他應用程式一樣。So once you download it, you'll install Power BI Desktop and run it like other applications on Windows. 下圖顯示 Power BI Desktop 的 [開始畫面],於應用程式啟動時出現。The following image shows the Start Screen of Power BI Desktop, which appears when you start the application.

Power BI Desktop 會連接到各種資料來源,從本機內部部署資料庫到雲端服務的 Excel 工作表。Power BI Desktop connects to a wide variety of data sources, from local on-premises databases to Excel worksheets to cloud services. 它會協助您清理及格式化資料以供使用,包括分割和重新命名資料行、變更資料類型,以及處理日期。It helps you clean and format your data to make it more usable, including splitting and renaming columns, changing data types, and working with dates. 您也可以建立資料行之間的關聯性,以便更輕鬆地建立模型和分析資料。You can also create relationships between columns so that it's easier to model and analyze your data.

開始使用 Power BI DesktopGetting started with Power BI Desktop

本主題中,我們深入查看 Power BI 的前兩個部分如何彼此搭配運作︰In this topic, we take a closer look at how the first two parts of Power BI fit together:

  • Power BI Desktop 中建立報表Create a report in Power BI Desktop
  • Power BI 服務中發行報表Publish the report in the Power BI service

我們將從 Power BI Desktop 開始,然後選取 [取得資料]。We’ll start in Power BI Desktop, and select Get Data. 即會出現資料來源的集合,讓您可以選擇資料來源。The collection of data sources appears, allowing you to choose a data source. 下列影像顯示如何選取網頁作為來源,而在上方的影片中,將選取 Excel 活頁簿。The following image shows selecting a Web page as the source, in the video above, Will selected an Excel workbook.

不論您選擇哪一個資料來源,Power BI 都會連接到該資料來源,並向您顯示可從該來源取得的資料。Regardless of which data source you choose, Power BI connects to that data source, and shows you the data available from that source. 下列影像是另一個範例,這個範例來自分析不同狀態及一些有趣淘汰統計資料的網頁。The following image is another example, this one is from a Web page that analyzes different states and some interesting retirement statistics.

在 Power BI Desktop [報表] 檢視中,您可以開始建立報表。In Power BI Desktop Report view, you can begin to build reports.

[報表] 檢視有五個主要區域:The Report view has five main areas:

  1. 功能區,顯示與報表和視覺效果相關聯的常見工作The ribbon, which displays common tasks associated with reports and visualizations
  2. [報表] 檢視或畫布,在這裡會建立和排列視覺效果The Report view, or canvas, where visualizations are created and arranged
  3. 底部的 [頁面] 索引標籤區域,可讓您選取或加入報表頁面The Pages tab area along the bottom, which lets you select or add a report page
  4. [視覺效果] 窗格,您可以在這裡變更視覺效果、自訂色彩或座標軸、套用篩選、拖曳欄位等等The Visualizations pane, where you can change visualizations, customize colors or axes, apply filters, drag fields, and more
  5. [欄位] 窗格,您可以在這裡將查詢項目和篩選拖曳至 [報表] 檢視,或拖曳至 [視覺效果] 窗格的 [篩選] 區域The Fields pane, where query elements and filters can be dragged onto the Report view, or dragged to the Filters area of the Visualizations pane

[視覺效果] 和 [欄位] 窗格可以摺疊,方法是選取邊緣上的小箭號,在 [報表] 檢視中提供更多的空間,以建置酷炫的視覺效果。The Visualizations and Fields pane can be collapsed by selecting the small arrow along the edge, providing more space in the Report view to build cool visualizations. 修改視覺效果時,您也會看到向上或向下的箭號,這分別表示您可以展開或摺疊該區段。When modifying visualizations, you'll also see these arrows pointing up or down, which means you can expand or collapse that section, accordingly.

若要建立視覺效果,只需要將欄位從 [欄位] 清單拖曳至 [報表] 檢視。To create a visualization, just drag a field from the Fields list onto the Report view. 在本案例中,讓我們來拖曳 RetirementStats 的 State 欄位,看看結果如何。In this case, let’s drag the State field from RetirementStats, and see what happens.

看看它...Power BI Desktop 會自動建立地圖式視覺效果,因為可辨識 State 欄位包含地理位置資料。Look at that... Power BI Desktop automatically created a map-based visualization, because it recognized that the State field contained geolocation data.

現在讓我們快一點,使用一些視覺效果建立報表之後,我們已經準備好將這份報表發行至 Power BI 服務。Now let’s fast-forward a bit, and after creating a report with a few visualizations, we’re ready to publish this to the Power BI service. 在 Power BI Desktop 的 [常用] 功能區上,選取 [發行]。On the Home ribbon in Power BI Desktop, select Publish.

系統將提示您登入 Power BI。You’ll be prompted to sign in to Power BI.

當您已登入且發行程序已完成時,會看到下列對話方塊。When you've signed in and the publish process is complete, you see the following dialog. 您可以選取連結 (在 [成功!] 下方) 以移至 Power BI 服務,您可以在其中看到您剛發行的報表。You can select the link (below Success!) to be taken to the Power BI service, where you can see the report you just published.

當您登入 Power BI 時,會在服務中看到您剛發行的 Power BI Desktop 檔案。When you sign in to Power BI, you'll see Power BI Desktop file you just published in the service. 在下列影像中,Power BI Desktop 中所建立的報表會顯示在 [報表] 區段中。In the image below, the report created in Power BI Desktop is shown in the Reports section.

在該報表中,我可以選擇釘選圖示將該視覺效果釘選到儀表板。In that report, I can choose the Pin icon to pin that visual to a dashboard. 下列影像顯示具有反白顯示方塊和箭號的釘選圖示。The following image shows the pin icon highlighted with a bright box and arrow.

當我選取該項目時,會顯示下列對話方塊,以將該視覺效果釘選到現有的儀表板,或建立新的儀表板。When I select that, the following dialog appears, letting me pin the visual to an existing dashboard, or to create a new dashboard.

從報表釘選幾個視覺效果時,即可在儀表板中看到它們。When we pin a couple of visuals from our report, we can see them in the dashboard.

當然,您還可以使用 Power BI 執行許多作業,例如共用您所建立的儀表板。There’s a lot more you can do with Power BI, of course, such as sharing the dashboards you create. 我們稍後在本課程會討論共用。We'll discuss sharing later on in this course.

接下來,我們將查看只要連接到雲端服務 (例如 Facebook、Salesforce 和許多其他項目) 即可自動建立儀表板的功能。Next, we look at a feature that can automatically create dashboards for you, just by connecting to a cloud service like Facebook, Salesforce, and many others.

連接至 Power BI Desktop 的資料來源Connect to data sources in Power BI Desktop

Power BI Desktop 可以連接至各種資料來源,包括內部部署資料庫、Excel 活頁簿和雲端服務。Power BI Desktop can connect to a whole range of data sources, including on-premises databases, Excel workbooks, and cloud services. 目前,超過 59 種不同的雲端服務 (如 GitHub 和 Marketo) 有特定的連接器,您可以透過 XML、CSV、文字和 ODBC 來連接一般來源。Currently, over 59 different cloud services such as GitHub and Marketo have specific connectors, and you can connect to generic sources through XML, CSV, text, and ODBC. Power BI 甚至能直接從網站 URL 抓取表格式資料!Power BI will even scrape tabular data directly from a website URL! 不過,我們還是從頭開始,先開啟 Power BI Desktop 並連接資料。But let's start from the beginning, with opening Power BI Desktop and connecting to data.

當您啟動 Power BI Desktop 並跳過開始畫面後,您可以從 [主資料夾] 索引標籤的功能區中選擇取得資料。When you start Power BI Desktop and move past the Start Screen, you can choose Get Data from the ribbon on the Home tab.

Power BI Desktop 每個月都有更新,而且 [Power BI Desktop What's New] (Power BI Desktop 新增功能) 頁面會獲得所有更新、部落格連結和下載連結的相關資訊。There are monthly updates to Power BI Desktop, and with each update, the Power BI Desktop What's New page gets updated with information about the updates, links to the blog, and a download link.

Power BI Desktop 中有各式各樣的可用資料來源。In Power BI Desktop, there are all sorts of different data sources available. 選取建立連接的來源。Select a source to establish a connection. 依選項不同,系統會要求您找到電腦或網路上的來源,或提示您登入服務驗證要求。Depending on your selection, you will be asked to find the source on your computer or network, or be prompted to log in to a service to authenticate your request.

連線後看到的第一個視窗是 [導覽器]。After connecting, the first window you'll see is the Navigator. [導覽器] 會顯示資料表或資料來源實體,按一下其中一個項目即可預覽其內容。The Navigator displays the tables or entities of your data source, and clicking on one gives you a preview of its contents. 接著立即匯入所選資料表或實體,或選取 [編輯] 在匯入前先轉換並清理資料。You can then import your selected tables or entities immediately, or select Edit to transform and clean your data before importing.

一旦選取想要帶入 Power BI Desktop 的資料表,您就可以選取 [導覽器] 右下角的 [載入] 按鈕,選擇將資料表載入 Power BI Desktop。Once you've selected the tables you'd like to bring into Power BI Desktop, you can choose to load them into Power BI Desktop by selecting the Load button in the bottom right corner of Navigator. 不過,有時候您可能想要先變更這些資料表,再載入 Power BI Desktop。There are times, however, where you might want to make changes to those tables before you load them into Power BI Desktop. 您可能只想要客戶子集合,或是篩選只出現在特定國家/地區的銷售資料。You might want only a subset of customers, or filter that data for sales that occurred only in a specific country. 在這些情況下,您可以選取 [編輯] 按鈕,先篩選或轉換資料,再一次帶入 Power BI Desktop。In those cases, you can select the Edit button and filter or transform that data before bringing it all into Power BI Desktop.

下節從編輯資料接續。We'll pick up there, and edit our data, in the next section.

使用查詢編輯器清理和轉換資料Clean and transform your data with the Query Editor

Power BI Desktop 包含的 [查詢編輯器],是功能強大的資料形塑和轉換工具,可隨時為模型和視覺效果服務。Power BI Desktop includes Query Editor, a powerful tool for shaping and transforming data so it's ready for your models and visualizations. 當您選取 [導覽器] 的 [編輯] 時,[查詢編輯器] 就會啟動並填入您從資料來源選取的資料表或其他實體。When you select Edit from Navigator, Query Editor launches and is populated with the tables or other entities you selected from your data source.

您也可以使用 [首頁] 功能區的 [編輯查詢] 按鈕,直接從 Power BI Desktop 啟動 [查詢編輯器]。You can also launch Query Editor directly from Power BI Desktop, using the Edit Queries button on the Home ribbon.

只要 [查詢編輯器] 載入可供塑造的資料,您就會看到幾個區段︰Once Query Editor is loaded with data that's ready for you to shape, you see a handful of sections:

  1. 在功能區中,現在有許多按鈕可以與查詢中的資料互動。In the ribbon, many buttons are now active to interact with the data in the query
  2. 在左窗格中,會列出查詢 (每份資料表或每個實體一個) 以供選取、檢視及形塑。In the left pane, queries (one for each table, or entity) are listed and available for selection, viewing, and shaping
  3. 在中央窗格中,會顯示所選查詢的資料以供成形In the center pane, data from the selected query is displayed and available for shaping
  4. [查詢設定] 視窗隨即顯示,列出查詢的屬性和套用的步驟。The Query Settings window appears, listing the query’s properties and applied steps

在中央窗格中,以滑鼠右鍵按一下資料行即會顯示數種不同的可用轉換,例如從資料表移除資料行、以新名稱重複資料行,以及取代值。In the center pane, right-clicking on a column displays a number of different available transformations, such as removing the column from the table, duplicating the column under a new name, and replacing values. 在這個功能表中,您也可以使用常用的分隔符號將文字資料行分割成多個。From this menu you can also split text columns into multiples by common delimiters.

[查詢編輯器] 功能區包含其他工具,例如變更資料行的資料類型、加入科學記號,或擷取日期元素,例如星期幾。The Query Editor ribbon contains additional tools, such as changing the data type of columns, adding scientific notation, or extracting elements from dates, such as day of the week.

套用轉換時,每個步驟都會出現在 [查詢編輯器] 右側之 [查詢設定] 窗格的 [套用的步驟] 清單中。As you apply transformations, each step appears in the Applied Steps list in the Query Settings pane on the right side of Query Editor. 您可以使用這份清單復原或檢視特定的變更,或甚至變更步驟的名稱。You can use this list to undo or review specific changes, or even change the name of a step. 若要儲存轉換,請選取 [首頁] 索引標籤的 [關閉並套用]。To save your transformations, select Close & Apply on the Home tab.

只要選取 [關閉並套用],[查詢編輯器] 就會套用您建立的查詢變更,將它們套用到 Power BI Desktop。Once you select Close & Apply, Query Editor applies the query changes you made, and applies them to Power BI Desktop.

在 [查詢編輯器] 中轉換資料時,您有各式各樣的事情可做,包括進階轉換。There are all sorts of things you can do when transforming data in Query Editor, including advanced transformations. 在下一節中,我們要探討幾個進階轉換,您會知道 [查詢編輯器] 提供您無數的資料轉換方法。In the next section, we take a look at a few of those advanced transformations, to give you a sense of the almost immeasurable ways you can transform your data with Query Editor.

更多進階資料來源和轉換More advanced data sources and transformation

我們會在本文探討一些 Power BI Desktop 的進階資料匯入和清理技術。In this article, we investigate some advanced data import and cleaning techniques for Power BI Desktop. 只要在 [查詢編輯器] 中塑造資料並帶入 Power BI Desktop,您就有幾種不同的查看方式。Once you've shaped your data in Query Editor and brought it into Power BI Desktop, you can look at it in a few different ways. Power BI Desktop 中有三種檢視:[報表] 檢視、[資料] 檢視和 [關聯性] 檢視。There are three views in Power BI Desktop: Report view, Data view, and Relationships view. 選取畫布左上角的各個檢視圖示,即可看到每一個檢視。You see each view by selecting its icon in the upper left side of the canvas. 下圖選取的是 [報表] 檢視。In the following image, Report view is selected. 圖示旁邊的黃色列表示作用中的檢視。The yellow bar beside the icon indicates which view is active.

若要變更檢視,只要選取其他兩個圖示的其中之一即可。To change the view, just select either of other two icons. 圖示旁邊的黃色列表示作用中的檢視。The yellow bar beside the icon indicates which view is active.

Power BI Desktop 可以在模型化程序期間,隨時將來自多個來源的資料合併到單一報表。Power BI Desktop can combine data from multiple sources into a single report, at any time during the modelling process. 若要將其他來源加入現有的報表中,請選取 [首頁] 功能區的 [編輯查詢],然後選取 [查詢編輯器] 的 [新來源]。To add additional sources to an existing report, select Edit Queries in the Home ribbon and then select New Source in Query Editor.

Power BI Desktop 可以使用許多不同的可能資料來源,包括資料夾。There are many different possible data sources you can use in Power BI Desktop, including Folders. 您可以連接到資料夾,一次匯入多個檔案的資料,例如一系列的 Excel CSV 檔案。By connecting to a folder, you can import data from multiple files at once, such as a series of Excel files of CSV files. 所選資料夾中包含的檔案在 [查詢編輯器] 中顯示為二進位內容,按一下 [內容] 資料行頂端的雙箭頭圖示可載入其值。The files contained within your selected folder appear in Query Editor as binary content, and clicking the double-arrow icon at the top of the Content column loads their values.

Power BI 最有用的工具之一是 [篩選]。One of Power BI's most useful tools is its Filters. 例如,選取資料行旁邊的下拉式箭號會開啟文字篩選檢查清單,您可以用它移除模型中的值。For example, selecting the drop-down arrow next to a column opens a checklist of text filters that you can use to remove values from your model.

您也可以合併和新增查詢,將多個資料表 (或不同資料夾的各種檔案資料) 轉換成單一資料表,只包含您想要的資料。You can also merge and append queries, and turn multiple tables (or data from various files, in folders) into a single table that contains just the data you want. 您可以使用 [新增查詢] 工具將新資料表的資料加入現有的查詢中。You can use the Append Queries tool to add the data from a new table to an existing query. Power BI Desktop 會嘗試比對以符合查詢中的資料行,您可依需要在 [查詢編輯器] 中調整。Power BI Desktop attempt to match up the columns in your queries, which you can then adjust as necessary in Query Editor.

最後,[新增自訂資料行] 工具會提供選項,讓進階使用者使用功能強大的 M 語言從頭撰寫查詢運算式。Finally, the Add Custom Column tool gives advanced users the option of writing query expressions from scratch using the powerful M language. 您可以 M 查詢語言陳述式為基礎新增自訂資料行,依所想展現資料。You can add a custom column based on M query language statements, and get your data just the way you want it.

清除不規則的格式化資料Cleaning irregularly formatted data

Power BI 幾乎可以匯入任何來源的資料,其視覺效果和模型工具最適合處理單欄式資料。While Power BI can import your data from almost any source, its visualization and modeling tools work best with columnar data. 有時候資料不會格式化成簡單的資料行 (通常是 Excel 試算表),而人眼感到舒適的表格配置不一定最適合自動化查詢。Sometimes your data will not be formatted in simple columns, which is often the case with Excel spreadsheets, where a table layout that looks good to the human eye is not necessarily optimal for automated queries. 例如,下列的試算表有跨越多個資料行的標頭。For example, the following spreadsheet has headers that span multiple columns.

幸好 Power BI 有工具可以快速將多欄資料表轉換成您可以使用的資料集。Fortunately, Power BI has tools to quickly transform multi-column tables into datasets that you can use.

調換資料Transpose data

例如,使用 [查詢編輯器] 的 [調換],您可以反轉資料 (將資料行變成資料列、將資料列變成資料行),以便將資料打散成您可以操作的格式。For example, using Transpose in Query Editor, you can flip data (turn columns to rows, and rows into columns) so you can break data down into formats that you can manipulate.

只要依影片所述做幾次,您的資料表就可以塑造成 Power BI 更容易使用的內容。Once you do that a few times, as described in the video, your table begins to shape into something that Power BI can more easily work with.

格式化資料Format data

您也可能需要格式化資料,以便 Power BI 能夠正確分類及識別它匯入的資料。You also may need to format data, so Power BI can properly categorize and identify that data once it's imported.

使用一些轉換,包括 將資料列升級成標頭 再中斷標頭,使用 [填滿] 將 null 值變成指定資料行上下欄中的值,以及 [取消資料行樞紐] ,您可以將資料清理到可在 Power BI 使用的資料集。With a handful of transformations, including promoting rows into headers into to break headers, using Fill to turn null values into the values found above or below in a given column, and Unpivot Columns, you can cleanse that data into a dataset that you can use in Power BI.

透過 Power BI,您可以對資料試驗這些轉換,並判斷哪些類型可以讓資料成為 Power BI 可用的單欄式格式。With Power BI, you can experiment with these transformations on your data, and determine which types get your data into the columnar format that lets Power BI work with it. 請記住,您採取的所有動作都會記錄在 [查詢編輯器] 的 [套用的步驟] 區段中,所以,如有轉換未依所想運作,您只要按一下步驟旁的 x 來復原它。And remember, all actions you take are recorded in the Applied Steps section of Query Editor, so if a transformation doesn't work the way you intended, you can simply click the x next to the step, and undo it.

建立視覺效果Create visuals

只要資料經過轉換及清理後成為 Power BI 可以使用的格式,您就可以開始建立視覺效果。Once your data is in a format that Power BI can use, by transforming and cleansing the data, you can begin to create visuals.

後續步驟Next steps

恭喜您!Congratulations! 您已完成本節的 Power BI 引導式學習 課程。You've completed this section of the Guided Learning course for Power BI. 您現在知道如何 將資料 放入 Power BI Desktop,以及如何 塑造轉換 該資料,以便建立吸引人的視覺效果。You now know how to get data into Power BI Desktop, and how to shape or transform that data, so you can create compelling visuals.

學習 Power BI 如何運作以及如何讓它 為您 工作的下一個步驟,是了解 模型 需要什麼。The next step in learning how Power BI works, and how to make it work for you, is to understand what goes into modeling. 如您所知, 資料集 是基本的 Power BI 建構元素,但有些資料集很複雜,其組成為許多不同的資料來源。As you learned, a dataset is a basic building block of Power BI, but some datasets can be complex and based on many different sources of data. 而且有時候,您需要在您建立的資料集中新增自己特有的觸控 (或 欄位 )。And sometimes, you need to add your own special touch (or field) to the dataset you create.

您在下一節中會了解模型和其他許多內容。You'll learn about modeling, and a whole lot more, in the next section. 下節見!See you there!

恭喜!

做得很棒!Way to go! 您已完成 Microsoft Power BI 引導式學習的取得資料一節。You've completed the Getting data section of Microsoft Power BI Guided Learning. 下一步是了解建立模型,這是下一個教學課程的主旨。The next step is to learn about Modeling, which is the subject of the next tutorial.

您已學到如何...

下一個教學課程

模型化Modeling

參與者

  • Davidiseminger
  • olprod
  • Alisha-Acharya