Excel Power Pivot可以直接在編輯器預先處理資料表。本文以傳票明細帳為例,介紹如何刪除新增資料行,在計算結果欄插入MONTH函數,建立日期資料相應的月份欄位。
目錄
Toggle一、刪除資料行指令
在Excel的Power Pivot取得資料之後,和Power Query一樣可以在編輯器預先處理資料。例如在這裡「備註」欄位在分析時並不需要,可以先選取這個欄位,然後在上方功能區切換到「設計」頁籤,選擇「資料行」中的「刪除」,可以參考一下浮窗的說明文字。
二、滑鼠右鍵執行
和熟悉的Excel操作一樣,除了在上方功能區執行指令,也可以如同截圖所示,先選取「關係人代碼」欄位,這個同樣沒有分析的需要,直接滑鼠右鍵,快捷選擇「刪除資料行」。
三、插入資料行操作
可以刪除不需要的資料,當然也可以新增想要的資料。例如這裡先選擇「傳票編號」,滑鼠右鍵快速「插入資料行」,兩位注意到「傳票編號」位於「傳票日期」後面,這個會用到。
四、插入MONTH函數
雖然說是新增資料,但是其實和Power Query轉換資料一樣,在Power Pivot主要是進行資料轉換或計算。以上個步驟所新增的資料行而言,預設名稱「計算結果欄」,而且會在第1個儲存格裡輸入計算公式,在此點一下公式編輯列的「fx」圖標,便會跳出「插入函數」視窗,於「選取類別目錄」中找到「日期與時間」,下面清單中找到「MONTH」這個函數,其作用為「傳回1(一月)到12(十二月)的數字,代表月份」。
五、引用資料行公式
沿續上個步驟插入函數將MONTH帶入公式編輯列後,在左括號後面加一個英文單引號「’」,此時公式為「=MONTH(‘),Power Pivot編輯器跳出目前資料模型可供選擇的資料表及資料行,在此選擇的是「’2020傳票明細帳[傳票日期]」,注意到它的語法和Excel表格幾乎相同,以時間歷程而言,可以說Excel Power Pivot是在Excel表格的基礎進行開發擴展而成的。
六、公式計算結果
設計好公式之後,公式計算結果很容易理解,便是計算出傳票日期相對應的月份。
七、資料行名稱修改
插入資料行預設名稱是「計算結果欄1」,顯然不是很好,可以直接在欄位名稱上面滑鼠左鍵連按兩下,準備修改欄位或者稱之為資料行的名稱。
八、資料行篩選確認
資料行名稱修改為「月份」之後,最後可以點選篩選器圖標,可以看到確實是一整個年度12個月份的資料,左下角顯示目前這個資料表總共有「39,729」筆資料。
Excel表格到Power Pivot
這篇文章的Power Pivot操作過程和Excel報表使用函數新增欄位非常類似,Excel同樣也有MONTH這個函數,公式結構、參數及作用完全相同,其實是因為微軟先在Excel開發了表格功能,接著因應大數據需求,將表格功能擴充為外掛Power Pivot工具,最後發展成熟再獨立為Power BI應用。這個大概是微軟從Excel到Power BI的過程。雖然中間過渡的Power Pivot可能實務上用到的機會較少,不過因為工具的設計概念是相通的,所以在操作Power Picot時對於微軟整個相關應用會更加有所有體會。
更多學習:Power BI文章合集。