なぜPowerQuery関数が便利なのか？Power BIの月の数字の頭に０を追加する方法が便利
目次
なぜPower Queryが便利な理由
Power Queryでは、データの取り込みやデータ変換、並び替え、列の追加といったデータ加工といったETL処理を行うことができます。
業務にて使用しているExcelデータを元ファイルとする場合、Excelファイルのファイル形式を変更せずにPower Queryにてデータ整形を行うことが可能となるため非常に便利な機能です。
また、PowerQueryにておこなった変更は「適用したステップ」に表示され、変更履歴を追うことも簡単に行えます。
今回は、条件式などを使用し列を追加する方法や取り込んだデータを加工する便利な方法をご紹介します。
列を追加する方法
まずは、Power Queryに取り込まれたデータに対し、条件列やデータ加工をした列を追加する方法をご紹介します。尚、Power Query内では、M言語が使用されます。
ですが、式を直接入力せずに列を追加することができる機能が用意されていますので、ぜひ活用してみてください。
文字の切り取り方法
最初に、文字の切り取り方法をご紹介します。Excelですと「LEFT」などの関数を使用するかと思いますが、Power Queryでは、「Text.Start」という式を使用します。
下記の日付列から左側4文字を切り取り「年」という列を新しく追加してみましょう。
まず、リボンの列の追加より「カスタム列」を追加します。
式を入力するバーが出てきますので、下記のように入力します。
文字の切り取りについては、ほかにも下記のものがあります。
文字の真ん中を切り取る方法：Text.Middle([日付],1,3) ←EXCELの「MIDDLE」関数と同様
実際に生成された列は下記のとおりです。
区切り文字による列の分割方法
Power Queryでは、区切り文字の設定を行うことも可能です。
例えば、下記の列を「:」にてセルの分割をしてみましょう。
リボンの変換タブより「列の分割」を選択します。
オプションがいくつかありますが、今回は「区切り記号による分割」を使用します。
尚、2-1で行った「年」を切り取る場合は、年は常に4文字ですので「文字数による分割」を使用することも可能です。
区切り記号をカスタムから「:」（コロン）を設定し、「:」が出現する度に列を分割するように設定します。
すると下記のように、列が分割されます。
文字の長さを揃える方法
続いて、月の頭に「０」をつけ、文字の長さを2桁で統一する方法をご紹介します。
カスタム列を追加し、下記のような式を入力します。
Text.PadStart([月],2,”0″)：月のカラムが2桁になるように頭に０をつける
※０をダブルコーテーションで囲むのを忘れないようにしてください。
また、文字の最後に追加し桁数を揃えたい場合は下記のようにします。
Text.PadEnd([月],5,”0″)：月カラムが5桁になるよう最後に０をつける
それぞれ作成された列は下記のとおりです。
頭に0を追加したカラムでは、すでに桁である10月～12月のカラムには、０が追加されず4月～9月のカラムのみ0が追加されていることが分かります。また、最後に0を追加したカラムでは5桁となるように、0が追加されていることが分かります。
条件式を使用する方法
続いて「条件列」を追加し、1月～6月に「上半期」7月～12月に「下半期」と入力された列を追加します。
カスタム列の追加にて式を入力することも可能です。
入力した式：if [月] <7 then “上半期” else “下半期”
すると、1月～6月に上半期、7月以降に下半期と入力された列が生成されます。
データを整形する方法
続いて、取り込んだデータを整形する方法をご紹介します。取り込んだデータに「null」がある場合やデータを集計する場合に役立つ機能です。
取り込んだデータにnullがある場合の対処法
今回は、業務にて使用している下記のExcelデータを取り込んだとします。
Power Query にてデータを確認してみると、グループ列に「null」というカラムができてしまいます。
原因は、Excelファイルにてセルを結合しているためです。日々の業務ではよくおこることかと思います。Excelではセルを結合するほうが見やすいですが、Power BIで使用するには「null」があるとビジュアルを作成することができません。ですので、Ppwer Queryにて、1行目の「A」を4行目の掃除機までコピーしてみましょう。
データをコピーする手順
リボンの変換タブより「フィル」を選択します。今回は、「A」「B」「C」というグループ名をそれぞれ下方向へコピーしたいので、「下」を選択します。
すると、下記のようにグループ名がコピーされ空欄のカラムがなくなりました。
日々使用するExcelでは、セルを結合していることもよくあると思います。Power Queryにて「フィル」の設定をしておくことで、ユーザー側のExcelシートを変更することなくデータの整形をすることが可能です。
グループ化し、集計する方法
続いて、グループ化し集計する方法をご紹介します。
先ほど使用したグループごとに売り上げ個数や金額を集計してみましょう。リボンの変換より「グループ化」を選択します。
今回は、集計したい項目がいくつかあったため「詳細設定」から設定を行いました。
売上金額と売上個数については「合計」を選択し、製品数は各グループにいくつの製品が属しているかですので「行数のカウント」を選択しました。
作成されたテーブルは下記の通りです。Excelで取り込んだデータとは見た目が全く異なりますが、Power BIにてビジュアルを使用する際は、こちらのほうが便利な場合も多いかと思います。
横持データを縦持へ変換する方法
最後に、横持ちデータを縦持ちデータに変換する方法をご紹介します。例えば下記のようなExcelを取り込んだとしましょう。
Power BIにてデータをビジュアル化するには、下記のように日付に対しデータが一つのほうが加工しやすいです。
ピボットする方法
縦持ちデータに変換する方法は非常に簡単です。
まず、Shiftキーを押しながら、縦持ちにしたいデータを選択します。
つづいて、変換タブより「列のピボット解除」を選択します。
すると、日付に対してデータが一つという縦持ちデータへの変換が完了です。
まとめ
Power Query で行う列の追加やデータ整形の方法はいかがでしたでしょうか。Power Queryは、業務にて使用するExcelファイル形式を変えることなく、データを加工することができう非常に便利な機能です。Power BIにて使用することができるビジュアルの幅も広がりますので、ぜひ活用してみてください。
フロッグウェル株式会社では、PowerBIの導入支援も行っています。お気軽にお問い合わせください！
＜Power BIハンズオンセミナー＞
弊社ではPower BIをはじめとするさまざまな無料オンラインセミナーを実施しています！
＞＞セミナー一覧はこちら
＜Power BIの導入支援＞
弊社ではPower BIの導入支援を行っています。ぜひお気軽にお問い合わせください。
＞＞Power BIの導入支援の詳細はこちら
＜PowerBIの入門書を発売中！＞
弊社ではPower BIの導入から基本的な使い方・活用方法の基礎などをわかりやすく解説した書籍も販売しています。
＞＞目次も公開中！書籍の詳細はこちら
