2019/4/30
勉強会にて発表した際、教えていただいた別法を追記しました。
最後の行を読み取ればよいだけの場合は、ぜひ別法にチャレンジしてみてください。
##想定するCSVファイル
列が変わってしまう様式のファイルです。会計システムから出る推移表の様式がこういうのだったりします。
ポイントは下記3点です。
- 1行目にヘッダがなく
- データ部分は同じ列数に揃っていて
- データ部分の列数よりも列数が多い行がない
Excelで開くとこんな感じ
メモ帳で開くとこんな感じ
株式会社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列より増えると、取り込めないデータが出てきてしまいます。
ソース = Csv.Document(File.Contents("C:~\CSVの取込調査\testData_推移表_7行.csv"),
[Delimiter=",", Columns=7, Encoding=932, QuoteStyle=QuoteStyle.None]
),
##Columns引数を消すとどうなるか
消したら、意外と勝手に判定すんじゃない？と思い、試してみました。7列のCSVなのに、取込結果は下記の通りです。1行目が1列しかないからです。
##コード例
最大列数を取ります。下記コードを詳細エディタに貼ると、関数になりますので、読みたいCSVファイルのパスのテキストを放り込んでください。
(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の説明文(コピペできなかったので画像です)
###ステップ
まずBinary.InferContentTypeを使うと、こんなRecordが得られます。
「Csv PotentialDelimiters」フィールドに入っているtableは下記の通り。
で、カンマ区切りの「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操作で取り込むと、こんな感じになります。
###2.ファイル形式の変更
ステップ「変更された型」を削除し、ステップ「ソース」の右端にある歯車のアイコンをクリックして、読み取るファイル形式をCSVからテキストファイルに変えてやります。
↓歯車アイコンを押すと出る、読取形式の選択画面
↓ファイル読取形式の変更後（デフォルトで、1列のテーブルになる）
###3.行の反転をする
変換タブの「行の反転」を押すと下図のようになります。
###4.列の分割をする
同じく変換タブの「列の分割」から、区切り記号による列の分割を選び、カンマで分割します。
列分割メニューの様子
###5.再度、行の反転をして、完成。
###追記のおまけ：コードで書く場合
コードで書くなら、最初からリストのままで操作してやって、テーブル化の段階で分割すればいいわけです。
※もちろん、ファイルパスのところは書き換えてください。
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 |