Power Pivot & Power Query 在不同介面上的 異與同

之前解釋過 Power BI desktop 與 Excel Power BI增益集工具的差異及發展，本來打算接下來的介紹都以Power BI Desktop為主。但後來想了一想，很多人/企業應該還是比較習慣使用Excel，而且我從Excel Power Pivot改成用Power BI Desktop之後，確實覺得有些小地方還是在Power Pivot中比較習慣，因此這邊在介紹Power Pivot & Power Query的差別的同時，也會介紹這兩個功能在Power BI Desktop與Excel Power BI 增益集工具裡的差異。

Query & Pivot的共同處: 都是將外部資料(檔案/資料庫)用連結的方式讀入Excel後再做處理，不論如何修改不會影響到原本的資料(Raw data)。同時因為是用連結的方式取得資料，未來若資料庫有新增資料筆數，只要在Excel上按下"重新整理"，資料就會直接進來。兩者的差別只在於Query會把整理好的資料放在一個新的sheet中；而Pivot則會把資料"隱藏"在檔案裡(不會在sheet裡出現，需要在檔案中打開Pivot才能看到)，只有在插入"樞紐分析表/圖"的時候才會看到插入的圖表。

Query: 讓資料清理這個動作規則化，讓未來新增的資料能夠快速與舊資料有一致的規則。大部份功能都可以用過去的Excel做到(利用公式、取代、篩選後手動修正等…)，說實話都不是什麼全新的功能。但差別就在於過去用Excel，每次新資料匯入都要重新做一次，花費大量時間在處理繁瑣的工作。新的Query則可以將所有的清理步驟記錄下來且標明每個動作的目的，未來不管是新增或管理規則都會相當容易。

Pivot: 第一個重點在於更大更快速的資料處理量。Excel從2007開始，Row上限筆數為1,048,576，而Power Pivot的上限則為1,999,999,997。(不過對我目前的中小型公司來說比較感覺不出差異，我想對一些線上流量大的公司來說應該就很明顯)

第二個重點則是"關聯性資料庫"的建立，這點是過去Excel所無法做到的(Access有類似的功能)。在關聯性資料庫的架構下，我們可以把 "明細資料表(Fact sheet)" 與 "維度資料表(Dimension sheet)"分開管理，這樣的架構在對整間公司的資料做管理時相當方便且實用。舉例來說，一間公司可能同時有50種產品、20間分店和100位銷售業務員，在觀察銷售數據時常常需要切換到不同的面向來分析，在傳統Excel裡，我們可能需要製作十來個樞紐分析表才能將銷售數據從各角度觀察清楚。但在關聯性資料庫的架構下，我們可以隨時切換不同的維度以觀察資料，甚至可以利用資料下鑽的功能來切換觀察資料時的高度。

另外值得一提的是，Pivot在Excel增益集中使用時，所有的量值(measure)式可以全部列在資料的下方，並依照自己的設定來排列。

以我自己在工作上來說，上面這兩個功能確實幫了相當大的忙。由其是Query的清理規則化 和 關聯式資料庫在不同維度間的轉換，不但節省做報表的時間，也因此讓我多了很多時間去思考更多真正重要的問題。