Excelシートの得意先コード一覧から得意先名一覧を取得


## やりたいこと

Excelの得意先mのシートには得意先コード一覧のTableがある。
得意先コード一覧から得意先名を取得したい。
得意先名はデータベースに格納されていて得意先コードを指定すれば取得できる。
得意先cdは1つ以上で増える場合も減る場合もある。


**Excelシートに入力されているデータ**

|得意先cd|
|---|
|1|
|3|
|5|

↓　Odbc.Queryでデータベースを呼び出す

![データベースアイコン1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24550/85aba850-937e-9a7b-09de-7880fa0ec957.png)

↓　データを取得

**取得したいデータ**

|得意先名|
|---|
|お得意先1|
|お得意先3|
|お得意先5|


## アイディア
得意先mのExcelシートから得意先cdをListにしてPowerQueryに取り込む。
それをText.Combineを使ってListをTextにする。
Textにしたリストは下記のように出力される。

```c#:Listからテキストに変換
 // Excelのシートから取得してListにしたもの
 List={"1","3","5"}
 // 変換
 CdText=Text.Combine(List,",")
```


```c#:出力結果
 "1,3,5"
```


この出力結果をText.ReplaceでSQL文を書き換える。

```c#:置換
 sql1=Text.Replace(sql,"_cd_",CdText)
```

```sql:SQL文を書き換えた結果
 select 
    customername 
 from customer 
    where customer in (1,3,5)
```

上記のSQL文をOdbc.Queryで実行すれば望むデータが取得できるであろう。

問題はExcelのデータをどうやってPowerQueryのListにするか。

## Excelのシートをリストに変換する

Excelのシートをリストに変換するには下記の手順でできる。


1. リストにしたい得意先cdのデータを選択して データ > テーブルから を押す。 
![fromtable.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24550/0301ba71-da57-0a91-205e-18e2ab0ca372.jpeg)

2. テーブルの作成でOKを押す。
![fromtable1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24550/75e34cc7-3f9a-187b-c6f3-f9448ca1e996.jpeg)

3. Teble.TransfromColumTypesのInt64.TypeをText.Typeに書き換えてEnterキーを押す。Int64のままだとText.Combine関数が動かない。
![fromtable2.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24550/c7b96206-e608-7b26-26cc-475fd11a27c3.jpeg)

4. 変更された型を選択してリストに変換を押す 
![fromtable3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24550/57232286-1ab8-608a-984e-8c2baeaccb5e.jpeg)

5. 得意先cdがリストに変換されました。右上のファイルを押します。
![fromtable4.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24550/6caee88a-3610-6e86-9947-e49af9b6cf01.jpeg)

6. 閉じて次に読み込むを選択
![fromtable5.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24550/6c6cfcf0-33da-6102-f049-60108e88a1cf.jpeg)

7. 「接続先の作成のみ」を選択して「読み込み」を押す。
![fromtable6.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24550/52f50220-281b-63f4-f758-4e47599cb218.jpeg)





## 成果
上記のアイディアを纏めて実行できるようにしたものが下記のM言語のコード。

```c#
 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文を変更する必要がなくなる。

## Reference

[Text.Replace](https://docs.microsoft.com/ja-jp/powerquery-m/text-replace)
[Text.Combine](https://docs.microsoft.com/ja-jp/powerquery-m/text-combine)

[【Power Query】動的SQLの結果を読み込む方法（Power BI）](http://www.programmingtheworld.com/index.php/2020/02/29/power-query-dynamic-sql/)

[Using a list as a parameter of an SQL query](https://community.powerbi.com/t5/Power-Query/Using-a-list-as-a-parameter-of-an-SQL-query/td-p/2444390)