やりたいこと
Excelの得意先mのシートには得意先コード一覧のTableがある。
得意先コード一覧から得意先名を取得したい。
得意先名はデータベースに格納されていて得意先コードを指定すれば取得できる。
得意先cdは1つ以上で増える場合も減る場合もある。
Excelシートに入力されているデータ
| 得意先cd |
|---|
| 1 |
| 3 |
| 5 |
↓ Odbc.Queryでデータベースを呼び出す
↓ データを取得
取得したいデータ
| 得意先名 |
|---|
| お得意先1 |
| お得意先3 |
| お得意先5 |
アイディア
得意先mのExcelシートから得意先cdをListにしてPowerQueryに取り込む。
それをText.Combineを使ってListをTextにする。
Textにしたリストは下記のように出力される。
// Excelのシートから取得してListにしたもの
List={"1","3","5"}
// 変換
CdText=Text.Combine(List,",")
"1,3,5"
この出力結果をText.ReplaceでSQL文を書き換える。
sql1=Text.Replace(sql,"_cd_",CdText)
select
customername
from customer
where customer in (1,3,5)
上記のSQL文をOdbc.Queryで実行すれば望むデータが取得できるであろう。
問題はExcelのデータをどうやってPowerQueryのListにするか。
Excelのシートをリストに変換する
Excelのシートをリストに変換するには下記の手順でできる。
-
リストにしたい得意先cdのデータを選択して データ > テーブルから を押す。
-
テーブルの作成でOKを押す。
-
Teble.TransfromColumTypesのInt64.TypeをText.Typeに書き換えてEnterキーを押す。Int64のままだとText.Combine関数が動かない。
-
変更された型を選択してリストに変換を押す
-
得意先cdがリストに変換されました。右上のファイルを押します。
-
閉じて次に読み込むを選択
-
「接続先の作成のみ」を選択して「読み込み」を押す。
成果
上記のアイディアを纏めて実行できるようにしたものが下記のM言語のコード。
let
// SQL文を定義
sql="select customername from customer where customercd in (_cd_)",
// Excelシートから得意先cd一覧をTableで取得
得意先cd = Excel.CurrentWorkbook(){[Name="得意先m"]}[Content],
// TableをListに変換
// Text型のListを作っている。
変更された型 = Table.TransformColumnTypes(得意先cd,{{"得意先cd", Text.Type}}),
cdlist = 変更された型[得意先cd],
// Text.Combineを使ってListをTextに変換
// _cd_ をTextに変換した得意先cdに置換
sql1=Text.Replace(sql,"_cd_",Text.Combine(cdlist,",")),
// Data取得
ソース = Odbc.Query("dsn=Database", sql1)
in
ソース
何がうれしいの？
Excelの得意先コード一覧に得意先cdを書き足せば得意先名一覧を増やせる。
得意先コード一覧の要らない行を削除すれば得意先名一覧を減らせる。
実際に使うエンドユーザーが自由自在に出力を調整できるようになる。
一般的な事務屋さんでPowerQueryやSQL文を操作できる人は少ない。
事「得意先が増えたので増やしてほしい。」
私「そのExcelシートに増えた得意先cd足しといて。」
これで終了。
僕がわざわざPowerQueryやSQL文を変更する必要がなくなる。