【初心者向け】Excel Power Query 004 データの加工

Excel Poewr Query

Excel Power Query データの加工

読者のみなさま!こんにちは!!Apachanです。
前回まではパワークエリを使ってExcelなど様々な形式のファイルからデータ取得を行いました。
参照するデータが異なっても基本的にはExcel上のリボンを操作するとデータ取得ができます。
今回は取得したデータの加工を解説していきます。RPAを扱う上で処理しやすいようにデータを整えることは非常に重要です。
パワークエリがRPAを運用する上でひとつの選択肢になれば幸いです。
もちろん、RPAを実施しない方も日常のちょっとした作業を自動化することができます。

目次
・事前準備
・パワークエリエディターによるデータ加工
・データの読み込み
・運用イメージ
まとめ

・事前準備

今回は実際の業務でもパワークエリを利用した案件をアレンジして紹介します。
あるシステムから抽出した商品の出荷情報を加工する業務となります。
ファイルについては下記の通りとなります。サンプルデータは必要であればダウンロードしてお使いください。
実際の業務で利用しているものは列や行がより膨大となっているため、
要点のみ伝わるように少し簡略化したものとなっています。

出荷情報.xlsx

<データ取得対象のファイルイメージ>
商品コードを略称に変換して出荷数量を集計
データ取得ファイルの利用列

それでは前回までの復習となるデータの取得から入っていきます。
出荷情報.xlsxの元データとは別で読み込むためのExcelファイルを用意します。
今回はデータ加工.xlsxとして新規作成します。

データ加工用Excelファイルを新規保存

Excelを開いた後にデータタブより以下手順で読み込んでください。

データの取得方法

ダイアログが起動するので出荷情報.xlsxのパスを選択してファイルを読み込んでください。

データ取得ファイルの選択

ナビゲーターが起動するので対象のシートを選択して、
「データの変換」をクリックすることでPower Query エディターを起動します。

ナビゲーター起動

下図のようにPower Query エディターが表示されれば準備はOKです。

PowerQueryエディター起動

・パワークエリエディターによるデータ加工

それではデータ加工に入っていきます。
基本的にはパワークエリエディターのマウス操作で加工を進めていくことになります。
今回は5つのステップで加工の手順を説明します。
※ M言語というものが分かればコードを記述することも可能ですが今回の内容では扱いません。

1. 一番最初に確認すべき事2点

プロパティの名前とデータ型は必ず確認しましょう。
今回のように読み込みデータが1つの場合は大きな影響はありませんが、実際の業務では複数ファイルを読み込みます。
名前を付けることで何を加工したものなのか一目で分かるように保持することが重要です。
また、各列のデータ型は項目名左のアイコンで確認してください。
今回は後続の作業で出荷数の合計を算出するため数値型である必要があります。
商品コードの方は文字列型で問題ありません。

始めの確認事項2点

データ型を変える際は項目名左のアイコンをクリックして適切なものを選んでください。

データ型の変更方法

2. 利用する列の選択(列の削除)

今回利用する列のみ残したいと思います。
基本的には不要な列を削除してけば問題ありませんが、今回のように不要な列の方が圧倒的に多い場合もあります。
その場合には下図の手順で残したい列だけ選択するという方法もあります。

利用列の選択(不要列の削除)

残したい列にチェックを入れてOKをクリックしてください。

残す列の選択

3. グループ化

「2. 利用する列の選択(列の削除)」は下図のように正常に処理が終わりました。
続いて同一商品コードに紐づく出荷数量の行が多数あるのでグループ化して集約したいと思います。
グループ化の手順は以下の通りとなります。

グループ化

グループ化の詳細を設定するダイアログが起動するので、
商品コードを出荷数量でグループ化できるように以下のように選択してください。
新しい列名は特に変える必要はありませんが、下図は分かりやすいように「新・出荷数量」としています。

グループ化詳細設定

一時的に「新・出荷数量」という名称にしましたが、処理が終わった後は不要なので「出荷数量」に戻ります。

項目名の変更

グループ化は下図のように正常に処理が完了しました。

グループ化実行結果

4. 必要な項目の選択

続いて必要な項目名の選択をします。
ここでは下図のようにapple, banana, cocoaの3つが必要とさせていただきます。

必要商品コードの選択

5. 商品コードの略称への置き換え

商品コードを略称へ変換します。
変換数が多ければ別でテーブルを作成してクエリをマージする選択肢もありますが、
今回変換対象が3つと限られているため以下の手順を取りたいと思います。

略称への変換

下図のように検索値と置換後の値を入力すればOKです。

置換方法

下図のように対象となる文字列が変換されます。

置換結果

同様の操作で残りの2つも設定を行い、下図のように完了しました。

全置換結果

・データの読み込み

データの加工が完了しましたら、読み込みの工程へと入ります。
下図のようにホームタブより「閉じて次に読み込む」を選択してください。

読み込み

「閉じて次に読み込む」を選択すると下図のように詳細を設定することができます。
データ加工した定義はテーブル形式で既存のシートにインポートしたいと思います。

読み込み方法の設定

下図のようにパワークエリエディターで作成した定義がシートへ反映すればOKです。

読み込み結果

・運用イメージ

上記のようにパワークエリエディターを使って設定すると繰り返し利用することが可能になります。
データ取得するファイルの構造(項目名や項目数など)が変わらないという前提ではありますが、
日常的に利用しているファイルで該当するものはあるのではないでしょうか。

下図のように同一構造でデータ量の異なるファイルを同一名称で上書き保存してください。
この操作で取得先となる参照データが差し変わります。

参照データソースの変更

先ほどパワークエリエディターで定義を作成した「データ加工.xlsx」の方に戻り、データタブの「すべて更新」をクリックしてください。

データ更新

データ取得先のファイルが差し変わったため、下図のように値が更新されます。
これで日常的な作業はワンクリックで業務完結します。

更新結果

まとめ

今回はパワークエリエディターを使ったデータ加工の方法から読み込みまでの流れを中心に解説しました。
一度パワークエリエディターを使って設定を済ませると、データ取得先のファイル構造に変更がない限りは繰り返し利用できます。
マクロ/VABのようにコードを記述するような知識がなくとも利用ができるので非常に使いやすい技術だと考えます。
次回はさらにデータ加工について深堀りしていきます。

Power Query – 概要と学習

Apachan

約10年間の代理店営業を経験した後にITに興味を持ちプログラミングを習得。営業で培ったコミュニケーション力を活かしつつ、様々な現場の業務改善に日々奮闘中。
RPAに限らず、SaaSやGAS, VBAなど手段はクライアントの方と考えながら進めます。
RPAやプログラミング関連の書籍も執筆しました。
お気軽にご相談ください。

関連記事