PowerQuery

PowerQueryの便利機能を使ってデータ整形してみた【ノンプロ件PowerQuery講座振り返り】

PowerQuery
猫雄
猫雄

どうも!猫雄です。PowerQuery初心者です。PowerQueryの勉強をはじめて3週間が過ぎました。

前回の記事では、Excelの同一ファイルでのクエリ作成手順全7工程を振り返りました。

クエリ作成手順全7工程

  1. データのテーブル化
  2. クエリの取り込み
  3. クエリ名の変更
  4. 自動処理されるデータ型変更の手順を削除
  5. 不要な列を削除
  6. データ型の変更
    ・・・・(クエリ整形いろいろ)・・・
  7. クエリの保存

今回は、工程⑥と⑦の間で行うクエリ整形いろいろに関する記事です。

ノンプロ研PowerQuery講座で学習した便利機能を振り返ります。

猫雄
猫雄

早速スタート!

PowerQueryの機能

 

ノンプロ研のPowerQuery講座で習った機能一覧は以下の通り。

  • 列のピボット
  • 列のピボット解除
  • 行列の入れ替え
  • 値の置換
  • フィル
  • 抽出
  • 書式 大文字・小文字
  • 書式 プレフィックス・サフィックス
  • 書式 トリミング
  • 書式 クリーン
  • 列の分割
  • 列のマージ
猫雄
猫雄

よし!復習も兼ねて実際に機能を使ってデータ整形してみるぞ!

 

整形するデータは、総務省の資料「統計表における機械判読可能なデータの表記方法の統一ルールの策定」の中から、NGとして例示されているサンプルデータを拝借します。

 

猫雄
猫雄

PowerQueryでデータをキレイにしてみよう!

NGサンプル① 数値データに、⽂字列が含まれている

修正前は、数値データに、「円」、「▲(マイナス表記)」、「,(カンマ)」が文字列として入力されています。そのため、SUM関数を使って合計値を求めようとするとエラーとなってしまいます。

猫雄
猫雄

よし!PowerQueryの機能「抽出」と「値の置換」を使って、修正後のデータになるように整形してみよう!!

抽出

講師の先生
講師の先生

抽出とは、テキストの値(文字データ)から文字を抽出する機能です。文字の数と区切り記号を使う方法があります。

データを見ると、「円」を区切り記号として、「円」よりも前のテキストを抽出できそうです。

対象列を選択し、リボンの「変換」から、「抽出」→「区切り記号の前のテキスト」をクリックすると、

「区切り記号の前のテキスト」ダイアログが開くので、区切り記号」に「」と入力し、「OK]をクリック。

「円」よりも前のテキストが抽出されました。
(※この時点ではデータ型は数値ではなくテキストです)

値の置換「テキスト → テキスト」

次に、「前回差分」列の「▲」を「-」(マイナス)に置き換えます。

「前回差分」列を選択し、リボンの「ホーム」から「値の置換」をクリックします。

値の置換ダイアログが表示されるので、
「検索する値」→「▲」
「置換後」→「-」(半角のマイナス)
と入力し、「OK」をクリック。

「▲」が「-(マイナス)」に置き換わりました。

最後に、「単価」「前回差分」「生産台数」のデータ型を「整数」に変更します

ヘッダー名の左端「ABC」をクリックすると変更できます。

これでデータ整形完了です。

元データと整形後の出力データ
整形後のデータを使えば集計ができます。

NGサンプル② 1セルに複数のデータが入力されている

修正前のデータは、1つのセルに2つのデータが含まれています。

猫雄
猫雄

これもPowerQueryを使って修正後のデータに整形してみよう。

ポイントは、元データに含まれる「(です。(を起点として列の分割を行います。

列の分割

講師の先生
講師の先生

列の分割は、テキストの値を複数の列に分割する機能です。

・区切り記号による分割

・文字数による分割

・範囲

と3つの分割方法があります。

今回は「 」を区切り記号として分割したいので、「区切り記号による分割」を使います。

対象の列を選択した状態で、リボンの「変換」→「列の分割」→「区切り記号による分割」をクリック。

「区切り記号による分割」ダイアログが開くので、
①「区切り記号を選択するか入力してください」→「カスタム」を選択し「」を入力。
②一番左の区切り記号を選択
③OKをクリック

」を起点に列が分割されました。

この時点で、「(」よりも前の売上金額部分のデータ型が「テキスト」から「整数」に自動的に変更し、「変更された型」というステップが生成されています。

猫雄
猫雄

ハイ、ハーイ!(挙手)

猫雄
猫雄
データ型はまとめて変更した方がステップ数が少なくて済むってことだったよね。

ってことで自動生成された「変更された型」のステップは削除します。

猫雄
猫雄

ステップの削除は簡単だな~。

 

分割されたヘッダー名をそれぞれダブルクリックし、「売上金額」と「費用総額」に名前を変更します。

次に、「費用総額」列の「」を取り除きます。やり方は、サンプル①で使用した「抽出」、「値の置換」、どちらを使用してもOKです。

猫雄
猫雄

今回は、値の置換を使ってみるか。

値の置換「テキスト → 空白」

「費用総額」列を選択し、リボンの「ホーム」から「値の置換」をクリックすると値の置換ダイアログが表示されるので、①「検索する値」→「
「置換後」→なにも入力しない
③「OK」をクリック。

」が取り除かれました。

最後に、「企業等数」「売上金額」「費用総額」のデータ型を数値に変換します。

まとめてデータ型を変更することで、ステップ数が1つで済みます。

 

元データと整形後の出力データ

NGサンプル③ セル結合している

セル結合を解除し、解除後のセルにもデータが入力されている状態にしたい場合、PowerQueryを使えば簡単に整形できます。

講師の先生
講師の先生

使う機能は「フィル」です。

フィル

講師の先生
講師の先生

フィルとは、選択列の「null」を、隣接する上の値(または下の値)で埋める機能です。

猫雄
猫雄

「null」ってなんだ?「空白」とは違うの?

講師の先生
講師の先生

「null」と「空白」は違います。

データには、目に見えるものと見えないものがあります。

見えないデータには

  • 空白
  • スペース
  • 改行
  • null

の4種類があります。
引用画像:ノンプロ研PowerQuery講座資料より

猫雄
猫雄

見えないデータって色々あるんだな~。

フィルは、見えないデータの中の「null」に対して有効です。

 

セル結合を含むデータをテーブル化→クエリ取込すると、2行目以下は「null」になっています。

次に対象列を選択し、リボンの「変換」→「フィル」→「下」をクリック。

1行目の「東京都」が下の行にコピーされました。

 

NGサンプル④ スペースが含まれている

猫雄
猫雄

体裁を整えるためにスペースを含める。あるある!

PowerQueryを使えば、スペースも簡単に取り除くことができます。

サンプルデータは、データ先頭にスペースが含まれているので、「トリミング」機能を使うと便利です。

トリミング

講師の先生
講師の先生

トリミングは、選択されている列の各セルから先頭と末尾の空白文字を削除します。

対象列を選択し、リボンの「変換」→「書式」→「トリミング」をクリック。

不要なスペースが取り除かれました。

NGサンプル⑤ 改行が含まれている

猫雄
猫雄

改行が含まれるデータもよくあるよね~。

改行も、PowerQueryの「クリーン」機能を使えば、簡単に取り除くことができます。

クリーン

講師の先生
講師の先生

クリーンは、選択されている列に含まれる印刷不可能な文字を削除する機能です。

猫雄
猫雄

「改行」はまさに印刷不可能な文字。

対象列を選択し、リボンの「変換」→「書式」→「クリーン」をクリック。

改行が取り除かれました。

猫雄
猫雄

たしかヘッダー名の「出荷本数」にも改行がふくまれてたよね?

 

猫雄
猫雄

「クリーン」は列に対して行う処理だから、行と列を入れ替えて、クリーンを行えばいいのかな?

行列の入れ替え

行列の入れ替えを行うにあたり、注意すべき重要ポイントが!

講師の先生
講師の先生

行列の入れ替えは、1列目と1行目が入れ替わります

つまり、ヘッダーに使用している行を1行目に下げる必要があります

リボンの「変換」→「1行目をヘッダーとして使用」→「ヘッダーを1行目として使用」をクリック。

ヘッダーが1行目に下りました。

次に、リボンの「変換」→「入れ替え」をクリックします。

行列が入れ替わりました。

次に、1列目を選択リボンの「変換」→「書式」→「クリーン」をクリック。

「出荷本数」から改行が削除されました。

再度行列を入れ替え、

リボン「変換」→「1行目をヘッダーとして使用」をクリック。

データ型を確認して完成です。

元データと整形後の出力データ

まとめと感想

以上、PowerQueryの便利機能を使ってデータ整形してみた【ノンプロ件PowerQuery講座振り返り】をお送りしました。

第3回の講座でもっとも印象に残ったのが、「見えないデータ」には種類があることです。

猫雄
猫雄

見えないデータ、今まで意識したことなかったな~

目には見えないけど存在するのは非常に厄介ですが、PowerQueryを学習することで、見えないデータにも意識が向かうようになりました。

また、今回総務省資料「統計表における機械判読可能なデータの表記方法の統一ルールの策定」の中から、NGとして例示されているデータを拝借しましたが、こちらの資料は以前Twitterでたいへんバズっていました。

猫雄
猫雄

たしかに、そもそもの元データがキレイに作られていたらいいよな~。

 

しかし!

PowerQueryの学習を始めて3週間が経過した今、

猫雄
猫雄

けっこうPowerQueryでなんとかなるんやないか!?

と思えてきました(*´▽`*)

猫雄
猫雄

次回はもう少し複雑なデータ整形に挑んでみます!

つづく…

 

 

 

 

 

 

 

この記事を書いた人
U-ROOm

システム開発会社にて、バックオフィス全般を担当する会社員です。

U-ROOmをフォローする
スポンサーリンク
シェアする
U-ROOmをフォローする
ゆるむ U-ROOm
タイトルとURLをコピーしました