Power BI入門教程

朱永光發表於2016-12-14

題記：這篇文章不僅是Power BI的入門教程，同時相對於Qlik Sense進行了簡單比較。

最近把一個Qlik Sense的示例應用手動轉成了Power BI的應用，把相關步驟和遇到的問題記錄如下，權當作一個入門教程。

1，準備原始資料

由於Qlik Sense的示例應用只有一個單獨的qvf檔案，那麼原始資料只有透過qvf來匯出。此qvf中的資料模型如下圖所示：

image

Qlik Sense並沒有提供資料模型包含資料的完整匯出功能，所以只能採用最麻煩和原始的方式，即：建立表格，在表格中新增某個資料表的所有欄位，然後再利用視覺化物件的匯出功能匯出資料為xlsx檔案。透過這種方式，把所有表都匯出。注：但在實際操作當中，未必所有表都匯出，因為有些表可以在Power BI中生成（比如日期表DateParts），有些表在Power BI中也用不到（比如月份順序表Month Sort Order）。

當然，如果你拿得到原始資料的檔案或者資料庫，那麼就可以跳過這一步。

2，載入資料

這步比較簡單，在Power BI中新建一個文件，透過“獲取資料”-“Excel”來選擇需要匯入的xlsx檔案。

image

選擇檔案之後，會顯示“導航器”對話方塊。在這裡選擇需要匯入的工作表（可以選擇多個）。選擇工作表之後，可以直接點“載入”，也可以點“編輯”來開啟“查詢編輯器”修改ETL指令碼（當然在載入之後還是可以重新編輯指令碼）。如果直接點“載入”之後，Power BI就會把選擇的工作表中的資料載入進來，這個時候就可以在“資料”檢視中預覽其中的資料，右側的“欄位”邊欄也會顯示錶及其包含的欄位：

image

這個時候，你會發現表名稱是xlsx檔案中預設的Sheet1，修改表名稱的最簡單方式就是，在表名稱上點右鍵選擇“重新命名”。另外，有些xlsx檔案匯入之後，資料可能會出現下面這種沒有正常識別列名稱的問題，這個時候就需要用到“查詢編輯器”中的“提升的標題”（把第一行資料當作標題/列名）的功能：

image

“查詢編輯器”是一個強大的UI操作介面，幫你自動生成Power Query的M語言指令碼，可以透過“高階編輯器”來檢視每個表的Power Query指令碼。而Power Query的第一步就是透過“源”和“導航”指令碼來實現Extract-Load的步驟。對於Power BI而言，Extract-Load可以實現的能力有：

  • 從各類資料來源中提取資料：各類檔案、各類資料庫、各類Azure的PaaS、各類聯機服務和其他資料來源（比如R指令碼）
  • 對資料來源的配置進行設定
  • 直接錄入資料

3，轉換和塑造資料

在載入資料的過程中或者之後，還可以繼續利用“查詢編輯器”來對載入的資料進行轉換和塑造（即Transform）。所謂轉換和塑造就是利用Power Query的M語言指令碼來對資料的載入過程，進行額外處理。我大致把這個過程中Power BI能提供的能力整理了一下：

  • 通用
    • 列的管理：選擇列、刪除列
    • 行的管理：保留行（前後、間隔、重複、錯誤）、刪除行
    • 排序：基於一個列或多個列進行升降序
    • 合併資料：可以是合併資料（兩個表提供不同的列），也可以是追加資料（兩個表提供不同的行）
  • 轉換
    • 表資料管理：對原始資料進行分組、提升第一行作為標題、行列顛倒、首尾行調換、對資料行計數
    • 所有列的處理：重新命名列名、資料型別的自動檢測和手動修改、替換值、填充單元格（上下兩個方向皆可）、透視列（正逆兩個方向）、轉換為列表（列表轉回列）
    • 文字列的處理：拆分（分隔符、字元數）、格式化（大小寫、首字母大寫、修整Trim、清除非列印字元、新增前字尾）、合併、提取（字串長度、首子字串、尾子字串、選定範圍子字串）、分析（從xml和json字串中提取出資料）
    • 數值列的處理：聚合運算（求和、最大最小、中值、平均值、標準偏差、值計數、非重複計數）、標準運算（四則、整除、取模、除得百分比、乘得百分比）、科學運算（求絕對值、求冪、求指數、求對數、求階乘）、三角函式運算、舍入（向上向下、自定義）、特徵（奇偶、符號）
    • 時間列的處理：日期的處理、時間的處理、持續時間的處理
    • 結構化列的處理：擴充套件、聚合
  • 新增計算列
    • 常規：基於公式計算、基於自定義公式計算、基於條件判斷計算、新增索引列、複製列
    • 基於文字列新增：格式化後、合併後、提取後、分析後
    • 基於數值列新增：聚合運算後、標準運算後、科學運算後、三角函式運算後、舍入後、提取特徵後
    • 基於時間列新增：日期處理後、時間處理後、持續時間處理後

從上面整理的內容來看，Power BI由於沿用了SQL Server和Excel中已經存在的Power Query，所以它的ETL功能還是非常強大的，並且幾乎不用你手動編寫ETL指令碼即可完成複雜的ETL工作。

想對Power Query的功能有詳細瞭解的，建議檢視Excel的文件：https://support.office.com/zh-cn/article/%E8%8E%B7%E5%8F%96%E5%92%8C%E8%BD%AC%E6%8D%A2%E5%9C%A8-Excel-2016-%E4%B8%AD-881c63c6-37c5-4ca2-b616-59e18d75b4de?ui=zh-CN&rs=zh-CN&ad=CN

4，建模資料

在完成資料的ETL之後，需要的步驟就是對資料進行建模。一般而言，在匯入資料之後，Power BI會根據欄位的名稱自動推斷出表之間的關係的。比如下圖就是匯入示例資料之後自動構建的關係圖：

image

建模的工作和ETL的工作是兩個不同的步驟，雖然有些功能達到的效果是一樣，但是背後實現的機理是不一樣的。最明顯的一個地方就是資料型別的修改，在查詢編輯器中對資料型別進行修改會替換或產生新的ETL指令碼，而在資料檢視中修改資料型別不會影響ETL指令碼。

Power BI支援的建模能力整理如下：

  • 管理關係：可以透過關係檢視來視覺化的拖動連線，也可以透過“管理關係”對話方塊瀏覽、新建、自動檢測、編輯和刪除關係 。
  • 關係：關係支援1:1、1：N、N：1，關係篩選支援單向和雙向 。
  • 公式計算：新增基於DAX表示式計算得到的度量值（度量值預設放到當前選中的表當中，也可以歸到其他表中），新增基於DAX表示式計算得到的計算列，新增基於DAX表示式計算得到的表 。
  • 排序：選擇排序的列 。
  • 格式設定：可以設定列的資料型別（小數、定點十進位制數、整數、日期/時間、日期、時間、文字、布林值、二進位制），顯示格式（各種貨幣格式、時間格式、百分比、小數位數、科學計數等） 。
  • 屬性設定：所屬表（可以為度量值設定歸屬的表），資料分類（設定值的顯示特徵，比如地址、Url、條碼，可以方便讓視覺化控制元件更好的處理內容），預設聚合方式（要不要求和等） 。
  • 安全性：基於DAX表示式來設定什麼角色可以檢視什麼資料，非常靈活的行級訪問控制機制 。
  • 分組：這是什麼鬼，我也還沒有搞懂。 有知道的朋友希望不吝賜教。

在建模的過程中，尤其和公式計算相關的東西都涉及到資料分析表示式（Data Analysis Expressions，DAX）的使用，詳細的說明可以參考MSDN官方文件：https://msdn.microsoft.com/library/gg413422.aspx

（updated 2016.12.20）更完整的文件在這裡：https://msdn.microsoft.com/en-us/library/mt244090.aspx

對於我的這個示例，我實際用到了如下幾種建模能力：

  • 日期資料的格式改變：把長格式的日期字串修改為短的 。
  • 各種度量值的建立：我的度量值主要涉及一些求和、求平均、計數、變化率等。為了更好的管理度量值，我特意新建了一個名為DetialsMeaured的表，公式為：DetailsMesured = ALL(Details[EpisodeID])，然後把建立的各種度量值歸到這個表當中 。
  • 百分比值的格式化：求變化率的度量值，可以把顯示格式設定為百分比，那麼在內建視覺化控制元件中就直接顯示為百分比，無需額外設定或者乘100（我使用了一個第三方控制元件，其無法識別百分比格式，只能在度量值上乘100） 。
  • 建立層級結構：為了支援資料的下鑽顯示，那麼需要建立資料的層次結構，比如財年包含月份。要建立層次結構很簡單，直接把一個欄位拖動到另外一個欄位下面Power BI就會自動建立一個新的層次結構列（包含了你剛剛操作的兩個欄位），接著可以繼續拖入其他列到這個層次結構列下面，還可以拖動來進行排序。
  • 建立日期表：很多分析都是和時間相關的，那麼就需要有一張獨立的日期表來為維度提供資料（包括年、財年、季度、月、日、天等）。原來的qvf中也存在這一個日期表，也是依靠指令碼生成的，對於Power BI而言同樣也可以透過指令碼來生成一個日期表。生成日期表的指令碼如下： 
DateKey = ADDCOLUMNS(
CALENDAR(FIRSTDATE(Details[EpisodeAdmissionDate]),LASTDATE(Details[EpisodeAdmissionDate])),
"DateAsInt",FORMAT([Date],"YYYYMMDD"),
"Year",YEAR([Date]),
"Quarter",VALUE(FORMAT([Date],"Q")),
"YearQuarter",FORMAT ( [Date],"YYYY" ) & "/Q" & FORMAT ( [Date],"Q" ),
"Month",MONTH([Date]),
"MonthName",FORMAT([Date],"mmm"),
"Day",DAY([Date]),
"WeekNum",WEEKNUM([Date]),
"WeekDay",WEEKDAY([Date]),
"WeekDayName",FORMAT([Date],"ddd"),
"Fiscal Year",IF(MONTH([Date])>3,YEAR([Date]) +1,YEAR([Date])),
"Fiscal Year Name",IF(MONTH([Date])>3,YEAR([Date]) & "-" & (YEAR([Date]) + 1),(YEAR([Date]) - 1) & "-" & YEAR([Date]))
)

把日期表新增到模型中後，就可以手動把日期表的Date欄位和Details表中的EpisodeAdmissionDate欄位建立其關係。最終的模型圖如下：

image

日期表建立的更多詳細介紹，可以參考這個部落格文章：http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

5，視覺化顯示資料

透過視覺化控制元件來顯示資料沒有太多可以說的。Power BI的官方文件已經寫的比較好了，見：https://powerbi.microsoft.com/zh-cn/documentation/powerbi-desktop-report-view/

無非就是把一個視覺化控制元件拖到報表頁面上，然後把所需的維度和度量拖到視覺化控制元件中的資料標籤頁中的“軸”/“圖例”或者“值”下面，如下圖所示：

image

資料建模的時候提到過下鑽顯示。下鑽顯示有兩種方式：一種使用層級結構列，一種不使用。

簡單說來，對於使用層級結構的方式，就是先建立一個層級結構列，然後把這個列拖到“軸”下面，視覺化控制元件就會在上面的操作欄顯示用於下鑽資料的特定按鈕。如下圖所示：

image

對於第二種下鑽顯示方式，我沒有采用，詳細的說明可以見：https://powerbi.microsoft.com/zh-cn/documentation/powerbi-service-drill-down-in-a-visualization/。

為視覺化控制元件準備好資料之後，還可以透過“格式”設定標籤頁（即刷子狀圖示）來設定一些顯示格式，比如標題，字型，顏色什麼的。

當資料新增到視覺化控制元件之後，這些欄位還會自動新增到視覺級別篩選器，除此之外，你還可以額外把欄位新增到頁面級篩選器和報告級篩選器中。這些篩選器的作用範圍望文生義應該可以理解。

不過需要注意的是，篩選器在公開發布到Web後並不可見，所以你還可以單獨在報表頁面上新增所需的切片器。切片器的資料設定方式和其他視覺化控制元件類似。

6，和Qlik Sense的比較

粗略的比較下來（個人觀點）：

  1. ETL能力由於背靠SQL Server分析服務的相關技術，所以可以做到和Qlik相當，尤其幾乎可以不用手寫指令碼。
  2. 建模能力和Qlik相當，只是操作體驗上會差一些。
  3. 視覺化能力比Qlik就差很多，不管是內建的視覺化控制元件還是第三方擴充套件的控制元件都是如此。
  4. 前端操作（也即終端使用者使用BI應用）的體驗也比Qlik差。
  5. 擴充套件和整合能力也由於Power BI的定位決定了沒有Qlik靈活。
  6. 最大的優勢是入門的許可費用低廉（如果願意使用SaaS的話）。
  7. 一大特色是具備人工智慧輔助的快速見解能力。

Power BI在視覺化能力方面確實需要進一步加強，比如我就遇到如下幾個問題：

  • 排序只能基於當前使用的維度，不能自定義排序
  • 堆積面積圖圖例不能下鑽
  • 沒有豎條儀表圖
  • 餅圖不能合併為Other
  • 表格不支援下鑽
  • （updated 2016.12.20）貨幣格式的數值不能顯示負數

最後不得不吐槽下，Power BI的文件用機器翻譯就算了，感覺Power BI Desktop的中文版也是機器翻譯的。還不如我來翻譯算了（作為MVP可以免費做貢獻）。

相關文章