贊贊小屋

Excel Power Query轉換資料:取消資料行樞紐及合併資料行

Excel有時候要將類似樞紐分析表彙總的報表拆解還原。本文以產品銷售統計為例,介紹Power Query取消資料行樞紐及合併資料行,再搭配VLOOKUP及CONCATENATE函數取得價目表單價。

一、價目表

泡沫紅茶店的價目表,很多產品單價都是如此呈現,一目瞭然。注意到已經在Excel建立了名稱為「價目表」的表格。

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第1張

二、產品銷售統計

如圖所示,表格裡已經有各項產品銷售數量,希望再填入單價,計算營業額。像這種場合,熟悉Excel讀者可能第一個想到VLOOKUP,無奈Excel常常是以欄位基準,VLOOKUP也是如此,面對上一步驟的價目表,VLOOKUP似乎無從下手。

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第2張

三、從表格建立查詢

上方功能區切換到「資料>取得及轉換資料」,執行「從表格/範圍」,作用是以此表格作為資料來源,準備帶入Excel Power BI進行處理。

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第3張

四、取消資料行樞紐

於Power BI編輯器上方功能區的「轉換>任何資料行」中,將「取消資料行樞紐」下拉,點選執行「取消其他行樞紐」。參考截圖所示的輔助說明文字,目前呈現綠色的「產品」這一欄是目前所選資料行,這個指令會將「大杯」、「中杯」、「小杯」這些其他內容都轉譯為成對的「屬性/值」。

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第4張

五、合併資料行

取消樞紐之後,會看到原本的「大杯」、「中杯」、「小杯」會依序逐一作為屬性和「咖啡」、「奶茶」、「紅茶」配對,同時將原本交叉的單價作為「值」資料行。

到這裡已經很接近理想狀態,但考量VLOOKUP函數特性是以單一欄作為條件,而這裡的「產品」和「屬性」是分開兩欄(Excel的欄是Power BI裡的資料行)。為了在Excel使用方便,上方功能區前往「轉換>文字資料行」點選執行「合併資料行」。

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第5張

六、合併資料行選項

在選項視窗保留預設值即可,注意到「新資料行名稱」是「已合併」,按「確定」。

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第6張

七、關閉並載入

果然第五步驟的「產品」和「屬性」連結在一起成為「已合併」資料行,例如「咖啡大杯」、「咖啡中杯」、…等。

到這裡已經在Power Query編輯器整理好資料,點選上方功能區的「檔案>關閉並載入」。

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第7張

八、整理好的價目表

回到Excel,多了一個表格,便是Power Query變形金剛後的價目表,相當漂亮。

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第8張

九、VLOOKUP及CONCATENATE函數組合

價目表整理好了,於產品銷售統計設計函數公式:「=VLOOKUP(CONCATENATE(A2,B2),價目表!A:C,2,0)」,先將「產品」及「大小」兩欄利用CONCATENATE函數合併,再以VLOOKUP將單價帶過來,順利完成任務!

Excel Power Query轉換資料:取消資料行樞紐及合併資料行 Power BI 第9張

Power Query裡乾坤大挪移般的取消樞紐

Excel建立樞紐分析表可以方便快速的彙總資料,Power BI裡的Power Query卻是反其道而行的取消樞紐,這個指令作用在習慣的Excel操作也許很難想像,如果讀者熟悉樞紐分析表,再參考這節範例「施展武功」的前後變化,應當能比較具體瞭解它的招式,也許在某些特定實務場合,會跟這篇文章一樣需要用上這套乾坤大挪移。