利用Power Query Editor自動複製並擴展二個表格

利用Power Query Editor自動結合並擴展二個表格, 將其合併為整個完整的表格. 如下圖:
圖A是來源, 訂單主表格, 共7個商品.
圖B是附加資料, 要加入到訂單裡的分店表格, 共十家分店.
圖C是結果, 結合了圖A和圖B裡的七個商品與十家分店, 每個分店ID都要訂這7個商品, 擴展成70筆商品.

如果自己複製, 就要複製十次(十家分店), 如果有100家.... 那複製的工作量就很可觀了, 還有可能不小心出錯, 前功盡棄!! 但如果利用Power Query Editor, 不論多大一張訂單, 有多少分店, Power Query Editor都可以幫你輕易做到.

首先, 把來源(訂單主表格-圖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] 工作頁上進行更新.