不要行の削除
Power Queryを使うと、CSVデータの読み込みが簡単になります。クラウド時代になって、基幹システムから提供されるCSVデータは巨大になりました。データの行数だけでなく、とにかく列数が多い。私のコンサルタント先でも「100列くらいあって、開くのも大変ですよ～」と笑っていました。もちろん、われわれExcelユーザーにとって必要なデータは、その100列すべてではないでしょう。その中から"この列とこの列と、あとこの列だけ使いたい"みたいなことが一般的です。ただ、そのように"特定の列だけ"をCSVファイルから読み込むのって、手動操作では厳しかったです。なので、以前は「マクロ(VBA)を使いましょう」というのが常識だったのですが、今だったら「Power Queryを使いましょう」です。もうね、VBAのコードをガリガリ書いて読み込む時代じゃありませんって。そっち方面が"得意中の得意"な私が言うのですから、これ間違いないです。
さて、そうしたCSVデータをPower Queryで読み込む話なのですが、今回は"不要な列"ではなく"不要な行"についてです。たとえば、サーバーから出力されるログファイルなどでは、下図のように、実データの前に何やらコメントなどの情報が記載されている場合があります。
データを分析するとき、上部のコメントは不要です。これらは自動的に削除したいものです。いや、CSVファイルだけではありません。Excelのブックであっても、下図のように"言語道断"な作りをしたデータを、しれっと渡されることもあります。
もうね、悪意があるとしか思えません。こんな表を作っておいて「私、Excelできますっ」的な顔をされると、ゴッドブローを叩き込みたくなります。データの中にメッセージ書くんじゃねーよって。まぁ、いずれにしても、こんな表をPower Queryで取得したときも、上部の不要行を削除したいです。CSVファイルでも、"極悪非道"ブックでも同じことですから、ここではCSVで話を進めます。
上図は、Power Queryエディタに取得したところです。こうした不要行を削除するには、削除する行を特定する何らかの情報が必要です。たとえば次のように
- 削除する行の行数が分かっている
- 削除する行のパターンが分かっている
もし「毎回先頭の7行を削除する」のように、毎回削除する行の"位置"と"行数"が決まっているのでしたら、Power Queryエディタの「行の削除」を使えます。
この「行の削除」には、「代替行の削除」のように意味が分かりにくい機能も含まれていますが、それはまた別のコンテンツでご紹介します。先頭の7行を削除するには「上位の行の削除」をクリックします。実行すると[上位の行の削除]という"悪い日本語のお手本"みたいなタイトルのダイアログボックスが開きますので、[行数]ボックスに7と入力して[OK]ボタンをクリックします。
この結果、先頭行には、本来タイトル(見出し)となる行が移動しましたので、この行をタイトル(見出し)として認識してもらいます。[1行目をヘッダーとして使用]をクリックします。
1行目がタイトル(見出し)として設定されました。このとき、Power Queryは各列のデータ型を自動的に判定します。今回は正しく、数値は「整数」で文字列は「テキスト」と認識してくれましたが、もしここで望まないデータ型を指定されたときは、適時自分で設定してください。
さて、ここまでは
- 削除する行の行数が分かっている
- 削除する行のパターンが分かっている
のうち、行数が分かっているケースでした。では、削除すべき行数が毎回分からないときはどうしましょう。そうなったら、しかたありません。削除すべきデータに関して、何らかのパターン(規則性)を見つけ出します。サーバーから出力されたログなどは、実データ以外のコメント的な行では、行頭に何かしらの記号が出力されることが多いです。今回のケースでしたら「**」です。
削除すべき行数は分からないけど、とにかく「**」で始まるデータは行削除したい、というのでしたら、フィルター機能を使います。データが入っている列(ここではColumn1)の▼をクリックしてください。表示されるドロップダウンリストで、[テキストフィルター]をポイントして[次の値で始まらない]をクリックします。
「次の値で始まらない」ボックスの右に「**」と入力して[OK]ボタンをクリックします。
不要な行が削除されますので、先と同じ手順で、1行目をタイトル(見出し)にしてください。
こんな感じですかね。今回例にしたCSV形式のデータは、一般的に「人が手で作る」のではなく、サーバーや基幹システムなどが「システム的に生成する」ことが多いです。なので、データとしては不要な、コメント的な行というのは、あらかじめ行数が決められていたり、行頭に何らかの記号などが出力されることが多いです。困るのは、冒頭にお見せした
みたいな"極悪非道"シートです。これ、明らかに毎回誰かが手で作ってますよね。想像ですけど、おそらくその人は、実データ以外のところ(ここでは、1行目から4行目まで)に、何が入力されていようか、そんなものまったく気にしていないのでしょうね。ちょっとでも文句を言おうものなら「だって、見れば分かるでしょ！(｀ﾍ´)」って逆ギレされそうです。Excelに、ってゆーかコンピュータには目がついていないので、見られないんですけどね。じゃ、こういうデータが毎回送られてくるときは、どーするのか？はい、諦めてください。無理ですね。何しろ、敵は自由ですから。何でもやってきますから。来月のデータは、タイトルが2行になっているかもしれませんし、コメントなのか実データなんだか、目で見ても分からないような変態的なシートを作ってこないとも限りません。この勝負、システム的に対応しようとしても絶対に勝てません。何とかして、相手を説得してください。陰ながら応援しています！