利用Power Query Editor自動結合並擴展二個表格, 將其合併為整個完整的表格. 如下圖:
圖A是來源, 訂單主表格, 共7個商品.
圖B是附加資料, 要加入到訂單裡的分店表格, 共十家分店.
圖C是結果, 結合了圖A和圖B裡的七個商品與十家分店, 每個分店ID都要訂這7個商品, 擴展成70筆商品.
首先, 把來源(訂單主表格-圖A)和附加資料(分店表格-圖B)都先設定成table模式.
做法: 選取表格範圍→Format as table
然後, 在來源(訂單主表格-圖A)頁, [Data]→點 [Get Data]→[From other sources]→[From Table/Range]此時, Excel會自動帶到 Power Query Editor 的畫面, 如下圖: 此時建議重新定名table然後依序[Data]→1.[Close & Load]→2.[Close & Load To]→3.[Table]→4.[New worksheet]→5.[OK]. 此時, Power Query Editor 會自動生成一張新的工作頁, 如下面.此時, 這張新增的工作頁將會自動複製現有的訂單主表格(圖A)給所有的分店(branch-圖B). 如何利用 Power Query Editor 自動複製現有的訂單主表格(圖A)給所有的分店(branch-圖B)?
首先, 到[branch]工作頁, [Data]→點 [Get Data]→[From other sources]→[From Table/Range]
同樣, 建議重新定名table, 然後依序[Data]→1.[Close & Load]→2.[Close & Load To]→3.[Only Create Connection]→4.[OK]. 如下圖:此時, 回到 Power Query Editor 剛才自動生成的工作頁 - OrderTable, 然後選1.[Query]→2.[Edit]此時, Excel會自動帶到 Power Query Editor 的畫面, 如下圖: 此時選取來源(訂單主表格-圖A): OrderTable, 然後選1.[Add Column]→2.[Custom Column], 此時會跳出 Custom Column 對話框→3.建議重新命名 new column name→4.在等號=後輸入附加資料(分店表格)的部份名→5.然後從選單中挑選正確的[BranchTable]→6.按下[OK]鍵, 此時 Power Query Editor 便會自動在 [OrderTable] 工作頁上進行更新.此時, 你會看到原始表格[OrderTable]現在新增了一欄資料[BranchData], 這裡的[BranchData]資料欄將允許你自BranchTable裡選取所需添加的欄位到原始表格中.
做法是: 點選1.[BranchData]右方小圖示→2.選取所需之項目欄→3.按下[OK]鍵即可. 此時你可以看到原來的OrderTable已經從原來的7行變成70行了(7行訂單列 X 10行分店碼=70)現在, 到1.[Home]→2.[Close & Load]→3.[Close & Load]
然後回到一開始 Power Query Editor 剛才自動生成的工作頁 - OrderTable, 你會看到原來的OrderTable已經從原來的7行變成70行了(圖C).
可參考實際操作2分半鐘的短片:然後回到一開始 Power Query Editor 剛才自動生成的工作頁 - OrderTable, 你會看到原來的OrderTable已經從原來的7行變成70行了(圖C).
0 留言