Power Queryで不要な行・列を削除する方法【データクリーニング】

Excel

みなさん,こんにちは。
シンノユウキ(shinno1993)です。

食品標準成分表をPower Queryで整形する方法について,連載形式で紹介しています。

前回の記事はこちら:

前回はPower Queryで複数のシートを縦に結合する方法を紹介しました。

今回はPower Queryで不要な行・列を削除する方法を紹介します。前回は複数のシートを結合し,データを読み込みましたが,不要な行・列が存在します。それらを削除していきましょう。

では行きましょう!

本記事は連載:「食品標準成分表をPower Queryで整形する方法」の一部です。前回までに行った作業を前提にしますので,連載の初回(食品標準成分表をPower Queryで整形する方法)からご覧いただくことをオススメします。

スポンサーリンク

Power Queryで不要な行・列を削除する方法

行を削除する方法

Power Queryでは行を削除する方法として多くの種類が用意されていますが,使用の頻度が高いのは下記の3つだと思います:

  1. 先頭から数行を削除:データの先頭から数行を削除します
  2. 途中から数行を削除:特定の行から数行を削除します
  3. フィルタリングによる削除:データのフィルタリングにより,行を削除します

このうち,食品標準成分表の整形で使用するのはです。正直,この2つさえ使いこなせれば,ほとんどのケースに対応可能だと思います。私が主に使っているのもこの2つです。これについて,下記で紹介します:

先頭から数行を削除する方法

行を削除する前に,ヘッダーとして残すべき行を決めましょう。食品標準成分表のExcelファイルを確認すれば分かりますが,栄養素項目名は階層構造になっており(炭水化物→利用可能炭水化物→差し引き法による利用可能炭水化物など),ヘッダー行として1行を選び出すのは困難です。そこで,栄養素項目名でなく,成分識別子をヘッダーとして残すようにすることとします。こうすることで,計算に使えるデータ形式として変換できます。

成分識別子は,国際的に利用できる食品成分識別子です。ユニーク(一意)なので重複する心配もありません。食品標準成分表の場合,一部,国際的に使用されているものとは異なる場合がありますが,ユニークな行としてこれを使用して差し支えないでしょう。ユニークでさえあれば,人が見て見やすいように復元することも難しくありません。

まず,先頭から不要な行を削除します。成分識別子を残すのであれば,1行目から10行目まで削除すればOKです。

メニューの「ホーム」から①「行の削除」②「上位の行の削除」をクリックします:

上位の行の削除の選択

ダイアログが開きますので,①行数で「10」を入力②「OK」をクリックします:

削除する行数の選択

下記のように,1~10行目まで削除されたテーブルになりました:

整形されたテーブル

次に,この段階で成分識別子のテーブルをヘッダーにしてしまいましょう。「ホーム」→「変換」にある「1行目をヘッダーとして使用」をクリックします:

1行目をヘッダーとして使用

これで,1行目(成分識別子)をヘッダーにすることができました。

ただし,これに付随して2つほどの作業が必要になります。

まず,列名を変更しましょう。ヘッダー行として昇格したセルに値が入っていなかったりして,列名がわかりづらくなっている列があります。下記に従い,それぞれの列を右クリック→「名前の変換」から名前を変更します:

元の列名変更後
1穀類食品群
Column2食品群番号
Column3食品番号
Column4索引番号
成分識別子食品名
Column63備考
列の削除

次に型の変更をステップから取り除きます。ヘッダー行に昇格させたことにより,自動的にそれぞれの列のデータ型が変更されてしまいました。この変更が問題ないことも往々にしてあるのですが,今回は自動的に型変換されると困る事情もあります。ですので,この型変換をステップから除外します。

右側の「適用したステップ」にある「変更された型」の左側にある「×」をクリックします:

変更された型の削除

これで変更された型をステップから除外することができました:

次にフィルタリングによる行の削除に移ります。

フィルタリングによる行の削除

さて,先頭行を削除した状態でも,まだ不要な行は残っています。今回は,複数のシートを1つのシートとして結合した形になっています。そのため,シートを結合部分に結合したシートの見出し部分が残ってしまっています:

まだ不要なデータが残っている

これをフィルタリングによって削除していきましょう。フィルタリングを使うことで,特定の値を含む列のみ残したり,逆に特定の値を含む行のみ削除したりできます。

筆者
筆者

行を削除する方法については,結局のところ,削除したい行を削除さえできれば,方法は何だって構いません。ただもちろん,手数を少なく・確実にできればベストです。

先に見たように,今回削除したい行の特徴として,「食品群番号」がnullであるということがあります。ですので,フィルタリングによって「食品群番号」がnullの行を削除してしまいましょう。

①「食品群番号」の列のフィルターボタンをクリック②nullのチェックを外します③「OK」をクリックします。

Power Queryのフィルター

これで不要な行を削除することができました!

列を削除する方法

最後に不要な列を削除します。今回の場合,下記が不要な列です:

  • 食品群(1列目):食品群番号があれば不要
  • Column16:エネルギーの算出に「利用可能炭水化物(単糖当量)」を用いたかどうかのフラグ(計算には不要)
  • Column19:エネルギーの算出に「差し引き法による利用可能炭水化物」を用いたかどうかのフラグ(計算には不要)
  • Column34:なぜか入っている空列(書籍用?)

これらの列を削除しましょう。といっても,行の削除と比べて列の削除は簡単です。

①それぞれの列を右クリック②「削除」を選択します:

列の削除

すべての列を削除したらOKです。これで不要な列を削除できました。

最後に,クエリ名を編集して,書き出しましょう。サイドバーにある「クエリの設定」→「名前」の箇所を「本表」にします

そして,ホーム→「閉じて読み込む」を選択し,Excelのテーブルとして書き出してみましょう。下記のように,2,478件のデータが読み込まれていれば,概ね問題ないかと思います:

もし失敗していたとしても,メニューの「クエリ」→「編集」から再度編集できます。このように,やり直しやすいのもPower Queryの魅力の1つです。

まとめ

今回は,Power Queryで不要な行・列を削除する方法について紹介しました。

結構面倒な感じに思えたかもしれませんが,上記の通りでなくても,最終的に不要な行・列が削除できてれば問題なしです。

次回は,Power Queryで値を置換する方法について紹介したいと思います。

連載目次

  1. 食品標準成分表をPower Queryで整形する方法【栄養士向け】
  2. 栄養士のためのPower Query基礎知識【できること・メリット】
  3. 栄養士のためのPower Query超入門【インポート・変換・書き出し】
  4. Power Queryで複数のシートを縦に結合する方法
  5. Power Queryで不要な行・列を削除する方法【データクリーニング】現在のページ
  6. Power Queryで値を置換する方法【完全一致と部分一致】
  7. Power Queryで食品標準成分表の別表のデータを整形する方法
  8. Power Queryで特定シートのみに含まれるレコードを抽出する方法
  9. Power Queryで複数のシートを横に結合する方法【クエリのマージ】
  10. Power Queryでファイルパスを相対パスで指定する方法
タイトルとURLをコピーしました