ExcelのシートにあるTableを全て結合して一つのテーブルにしたい。
itemcd1 itemcd2 itemcd3と3つのテーブルがあってそのテーブルを結合したい。
PowerQuery一発でできないだろうか？
出来ました。
下記のように書きます。
let
// ブック内の全テーブルを取得
Source = Excel.CurrentWorkbook(),
// itemcdから始まるテーブルだけを取得
フィルターされた行 = Table.SelectRows(Source, each Text.StartsWith([Name], "itemcd")),
// 取得したTableを全て結合
SelectedTables = Table.Combine(フィルターされた行[Content])
in
SelectedTables
何がうれしいの？
例えば下記のようにシートがあるとしましょう。
Sheet1シート
| 商品cd | 商品名 |
|---|---|
| 1 | = XLOOKUP(A1,商品m[商品cd],商品m[商品名],"",0,1) |
| 2 | = XLOOKUP(A1,商品m[商品cd],商品m[商品名],"",0,1) |
商品cdの列は テーブル名 itemcd として定義されています。
商品mシート
| 商品cd | 商品名 |
|---|---|
| 1 | Nintendo Switch2 |
| 2 | iPhone17 |
商品mシートはテーブル名 itemcd の商品cdから該当する商品名をデータベースから取得するようにしてあります。
そして 商品m というテーブル名になっています。
Sheet1に商品cdを入力すると、
商品mシートの商品m テーブルにデータベースからデータを取得し、
XLOOKUPで商品名を表示されます。
これをそのままSheet1をコピーすると、
Sheet1(1)シート
| 商品cd | 商品名 |
|---|---|
| 1 | = XLOOKUP(A1,商品m[商品cd],商品m[商品名],"",0,1) |
| 2 | = XLOOKUP(A1,商品m[商品cd],商品m[商品名],"",0,1) |
商品cdの列は テーブル名 itemcd1 と定義されます。
itemcdテーブルはitemcd1という名前のテーブルになります。
itemcdから始まるテーブルは全て結合して商品cdを取得
その商品cdからデータを取得するようにします。
そうするとコピー先の商品cdの欄に商品cdを入力すると勝手に商品cdを取得するようになります。
テーブルの商品cdからデータベースのデータを取得する方法は、
Excelシートの得意先コード一覧から得意先名一覧を取得
の投稿を参考にしてください。
そんなPowerQueryのコードが下記のようになります。
let
// ブック内の全テーブルを取得
Source = Excel.CurrentWorkbook(),
// itemcdから始まるテーブルだけを取得
フィルターされた行 = Table.SelectRows(Source, each Text.StartsWith([Name], "itemcd")),
// 取得したTableを全て結合
SelectedTables = Table.Combine(フィルターされた行[Content]),
変更された型 = Table.TransformColumnTypes(SelectedTables,{{"商品cd", Text.Type}}),
//商品cdをList化
商品cdList = 変更された型[商品cd],
sql="
select
商品m.商品cd
,商品m.商品名
FROM
商品m
WHERE
商品m.商品cd in (_cd_)
",
// 商品cdのListを,の付いたTextに変換し _cd_ に置き換える。
sq11=Text.Replace(sql,"_cd_",Text.Combine(商品cdList,",")),
// 商品データ取得
ソース = Odbc.Query("dsn=DSNName", sq11)
in
ソース
商品mのデータをシートに全て入れちゃダメなの？
弊社、商品アイテム10万点以上あります。
現在のExcelであれば全て入れることはできますが、
Excel、1ファイルなのに30MB以上のビックデータになってしまいます。
メールにも添付して送れない扱いにくいデータになってしまいます。
そのため必要なデータだけを取得するように上記の仕組みを整えてあります。