CSVファイルの列数が変わっても、クエリ編集なしで対応するアイデア

2019/4/30
勉強会にて発表した際、教えていただいた別法を追記しました。
最後の行を読み取ればよいだけの場合は、ぜひ別法にチャレンジしてみてください。


##想定するCSVファイル
列が変わってしまう様式のファイルです。会計システムから出る推移表の様式がこういうのだったりします。
ポイントは下記3点です。

- **1行目にヘッダがなく**
- データ部分は同じ列数に揃っていて
- データ部分の列数よりも列数が多い行がない

Excelで開くとこんな感じ
<img width="572" alt="コメント 2019-03-31 180718.png" src="https://qiita-image-store.s3.amazonaws.com/0/370992/4af4f028-d5ff-0a0b-0349-afadf2bc18cc.png">

メモ帳で開くとこんな感じ
>株式会社A社
税抜
コード,名前,2018年10月累計,2018年11月累計,2018年12月累計,2019年1月累計,2019年2月累計
001,甲,100,100,100,0,0
002,乙,200,200,200,0,0
003,丙,300,0,0,0,0

※3行目以下のデータ部分は記事の最後にも載せています。

##UIでクエリを作るだけだとどうなるか
上記のCSVファイルをUI操作で取り込み詳細エディタを見ると、引数に「Columns=7」と入ります。列数7列固定で取り込むということです。
なので、7列より増えると、取り込めないデータが出てきてしまいます。

```PowerQuery:UIでの取込でできるコード
ソース = Csv.Document(File.Contents("C:~\CSVの取込調査\testData_推移表_7行.csv"),
                            [Delimiter=",", Columns=7, Encoding=932, QuoteStyle=QuoteStyle.None]
                            ),
```
##Columns引数を消すとどうなるか
消したら、意外と勝手に判定すんじゃない？と思い、試してみました。7列のCSVなのに、取込結果は下記の通りです。1行目が1列しかないからです。
<img width="137" alt="コメント 2019-03-31 171237.png" src="https://qiita-image-store.s3.amazonaws.com/0/370992/479c57af-ba85-a6d2-a91c-5c5b04672ba4.png">

##コード例
最大列数を取ります。下記コードを詳細エディタに貼ると、関数になりますので、読みたいCSVファイルのパスのテキストを放り込んでください。

```PowerQuery:最大列数取得
(csvファイルパス as text)=>
let
    ソース = Binary.InferContentType(
                File.Contents(csvファイルパス)
                ),
    #"Csv PotentialDelimiters" = ソース[Csv.PotentialDelimiters],
    フィルターされた行 = Table.SelectRows(#"Csv PotentialDelimiters", each ([PotentialDelimiter] = ",") and ([QuoteStyle] = 0)),
    MaxColumns = フィルターされた行{0}[MaxColumns]
in
    MaxColumns
```

###関数：Binary.InferContentType
この関数の存在は @PowerBIxyz さんに教えていただきました。
ありがとうございます。

>sharedの説明文(コピペできなかったので画像です)
<img width="614" alt="コメント 2019-03-31 165327.png" src="https://qiita-image-store.s3.amazonaws.com/0/370992/27a8c5ad-22f5-7ac5-f2cc-4739cee33e7b.png">

###ステップ
まずBinary.InferContentTypeを使うと、こんなRecordが得られます。
<img width="173" alt="ソース.png" src="https://qiita-image-store.s3.amazonaws.com/0/370992/f2e21a3c-6de2-c860-3bf4-09bcab381800.png">

「Csv PotentialDelimiters」フィールドに入っているtableは下記の通り。

<img width="628" alt="展開したテーブル.png" src="https://qiita-image-store.s3.amazonaws.com/0/370992/87d50b07-1bd6-4a0c-baa0-9a1e68b4655b.png">

で、カンマ区切りの「MaxColumns」列(1,2行目)を見ると、7となっていますよね。
1行目も2行目も違いがないので、コード例では1行目の方で使っています。
この辺りの設定は、得られたtableを見ながら、設定してみてください。
###コードの使用例
Columns引数に固定値の代わりに、上記の関数で取った数を入れてやります。

```
let
    ソース = Csv.Document(    
                csv_bin,
                [Delimiter=",",
                 Columns=最大列数取得("C:～\CSVの取込調査\testData_推移表_7行.csv"),
                 Encoding=932])
in
    ソース
```
##追記：別法～行反転を使う
最後の行が取りたい列数を備えていることが分かっている場合、UI操作でも列数不定のファイルに対応することができます。

###1.CSVを読み取る
UI操作で取り込むと、こんな感じになります。
![ただ取り込んだ状態.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/248d7bc2-d30c-2d57-dfed-5414712f9378.jpeg)


###2.ファイル形式の変更
ステップ「変更された型」を削除し、ステップ「ソース」の右端にある歯車のアイコンをクリックして、読み取るファイル形式をCSVからテキストファイルに変えてやります。

↓歯車アイコンを押すと出る、読取形式の選択画面
![形式選択.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/39cb3cc6-9f57-2f9d-0bc1-7e9f454a0b3a.png)

↓ファイル読取形式の変更後（デフォルトで、1列のテーブルになる）
![形式変更後.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/ad0e9f3d-22e6-f1b0-f0cd-aac1c0badf16.png)


###3.行の反転をする
変換タブの「行の反転」を押すと下図のようになります。
![反転操作.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/78dbe908-ced3-ebe3-d815-f292f1d7c296.jpeg)

###4.列の分割をする
同じく変換タブの「列の分割」から、区切り記号による列の分割を選び、カンマで分割します。
![列分割操作.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/4809f5e1-7585-fc53-6012-c4afca7846d2.png)

列分割メニューの様子
![列分割メニュー.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/e674a94e-213c-5e68-39c8-0da92aab6738.jpeg)

###5.再度、行の反転をして、完成。
![完成図.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/e48ffb30-92b9-e7a0-d0e1-b43dab735d3f.png)


###追記のおまけ：コードで書く場合
コードで書くなら、最初からリストのままで操作してやって、テーブル化の段階で分割すればいいわけです。
※もちろん、ファイルパスのところは書き換えてください。

```
let
    ソース = Lines.FromBinary(File.Contents(ふぁいるぱす), null, null, 932),
    反転された行 = List.Reverse(ソース),
    テーブルに変換済み = Table.FromList(反転された行, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
    反転された行1 = Table.ReverseRows(テーブルに変換済み)
in
    反転された行1
```


##試した環境
Win7,10―PowerBI desktop,Excel(Office365)

##おまけ：サンプルデータ
|コード	    |名前	|2018年10月累計	|2018年11月累計	|2018年12月累計	|2019年1月累計	|2019年2月累計 	|
|:--------- |:----------|--------------:|--------------:|--------------:|--------------:|--------------:|
|001	    |甲		|100		|100		|100		|0		|0		|
|002 	    |乙		|200		|200		|200		|0		|0		|
|003	    |丙		|300		|0		|0		|0		|0		|
|004	    |丁		|400		|0		|0		|0		|0		|
|005	    |戊		|500		|500		|500		|500		|500		|
|006	    |己		|600		|600		|600		|600		|600		|
|007	    |庚		|700		|700		|700		|700		|0		|
|008	    |辛		|800		|800		|800		|800		|0		|
|009	    |壬		|0		|0		|0		|0		|0		|
|010	    |癸		|0		|0		|0		|0		|0		|
|011	    |甲		|0		|0		|0		|200		|200		|
|012	    |乙		|0		|300		|300		|300		|400		|
|014	    |丁		|0		|500		|500		|500		|500		|