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で特定シートのみに含まれるレコードを抽出する方法
タイトルとURLをコピーしました