PowerQuery-列数が多い累積データを個別データに戻す

サブクエリをメインクエリにマージ Excel(エクセル)
Excel(エクセル)

Excel / PowerBIでは、Power Queryを使用してデータを取得します。

累積を計算する方法はいろいろ載っているのですが、累積されたものから個別データに戻す方法は、あまり見ないのでまとめてみました。

例として、厚労省オープンデータの「新型コロナ死者数(累積)」を使用します。

オープンデータ
スポンサーリンク

クエリ1:ソースを単純に読み込んだもの

この手順では、ソースデータを2回読み込みます。

そのまま組むと、データソースへのアクセス数が不必要に増えてしまう可能性があるので、まずソースを生のまま読み込んだクエリをひとつ作ります。

Webから読み込む

これ自体をワークシート上に展開する必要はないので、「接続の作成のみ」

接続の作成のみ

プレビューで見ると、このようなデータです。

厚労省オープンデータ 新型コロナ死者数 を読み込んだプレビュー

例えば、5月10日に各都道府県で発表された死者数は
5月10日の発表数 - 5月9日の発表数 で得られます。

この計算を、Power Query上で実現しようというわけです。

全都道府県分のカスタム列を作るのは、さすがに非合理的

インデックス列を追加するなどして、引き算をカスタム列として追加すればいいように見えますが、それは列数が少ない場合です。

47都道府県+全国ですから、48列あります。48個カスタム列をじゅんじゅんに追加するのは、さすがに参ってしまうわけです。

そこで次のような方法をとりました。

スポンサーリンク

クエリ2:クエリ1を参照し、日付を一日ずらす

そこでどうしたかというと、もう一個サブクエリを使いました。

まずは、いま作った、データソースのためのクエリを右クリック→「参照」します。

クエリの参照

こうすることで、データソースを再び読み込むことなく、同じデータを参照できるようになります。

日付を一日ずらしたカスタム列を作る

ここで、「日付に1日を足したもの」をカスタム列として作成します。

=[Date]+#duration(1,0,0,0)

日付型に足し算する場合は、エクセルのように単純に1は足せません。 #duration(日,時,分,秒)を使います。

そして、できたカスタム列を「先頭に移動」します。

先頭に移動

これで、「【カスタム】の日付のデータから引き算すべきデータ」ができます。

Date列を削除し、「カスタム」列を新たに「Date」という名前に変更しておきます。これは、次の手順の準備です。

Date列を削除
カスタム列をDateに変更

「その他の列のピボット解除」

続いて、Date列を除くすべての列を「ピボット解除」して、列数を削減します。

その他の列のピボット解除

データ列が一列だけになり、計算しやすくなりました。

ここまでが、サブクエリの準備です。これを使って、メインクエリを組みます。

スポンサーリンク

クエリ3:結果を出すためのメインクエリ

メインクエリも、同じソースクエリを「参照」して作成します。

参照
参照したクエリ

このまま「その他の列をピボット解除」して、計算できる状態にする

こちらは、日付をずらさずに、このままDate列を選択して「その他の列をピボット解除」します。

その他の列のピボット解除
ピボット解除したクエリ

さきほどとよく似たデータですが、こちらは厚労省が発表したままの日付になっています。

サブクエリと「マージ」

ここで、[Date]と[属性]をキーにして、さきほど作ったサブクエリと【マージ】します。

クエリのマージ

一致している行数が少ないと思いますが、これは一日ずらしたために、ちょうど一日分の不一致が出ているわけですね。

実行するとこのようになります。

マージされたテーブル

テーブルを展開してみます。

必要なのは[値]列だけなので、チェックは[値]だけにつけます。元のテーブルの[値]とだぶるので、元の列名をプレフィックスとして使用して回避します。

テーブルの展開

実行するとこのようになります。

テーブルを展開

元のデータには、2020/5/9のデータがありましたが、テーブルを展開すると、5/9のデータから引き算するべきデータは存在しなかったため、削除されて、5/10からのデータに変わりました。

もともと、2つのクエリの並び順はたぶん一致しているんですが、日付と属性(都道府県名)でマージしているため、仮に並び順がちがっていても、正確に、都道府県別の当日・前日の累積数が一レコードに並ぶことになります。

ふたつのデータを引き算して、カスタム列に日毎発表数を得る

あとは簡単ですね。両方の数字を引き算するカスタム列を作成します。

引き算のカスタム列

欲しかった数字がでましたので、計算に使った列はもう必要ありません。削除して、列名をととのえます。

列の削除
スポンサーリンク

完成したデータ

以上の手順で、都道府県別の累積データから、日毎の発表数データを作ることができました。

都道府県別の日毎発表数

ワークシートに読み込んでみます。「閉じて読み込む」

ワークシートに読み込まれたデータ
スポンサーリンク

追記:都道府県名を日本語になおす

追記として、厚労省データでは都道府県名が英語表記になっているので、これを日本語になおしてみます。

都道府県名の英語→日本語の一覧表をつくり、クエリとして読み込む

まず、面倒ですが都道府県名の英語→日本語の対照表を作ります。

Google検索すると、そのような表を公開してくれているサイトがいくつかあるので、そこからコピペでいただいてもよいかもしれません。

当店の場合は、しょっちゅう使うので、どこかのブックにはいっているものをコピーしてきました。

表を作ったら、テーブルにしてテーブル名をつけておくと、読み込んだ時この名前のクエリになるので楽です。

都道府県名の英語と日本語の一覧

できたら、これを「テーブルまたは範囲から」で読み込みます。

テーブルまたは範囲から
都道府県名をクエリとして読み込み

これを改めてワークシートに読み込む必要はないので、「読み込み先」を「接続の作成のみ」にしておきます。

一覧表を使ってテーブルの列を一括置換する

この一覧表を使って、テーブルの列を一括置換します。

こちらのサイトを参考にさせていただいています。

「複数の語句をまとめて置換する」を実務で使おう|Power Query - Qiita
大変お世話になってる狸(@tanuki_phoenix )さんのこの置換記事の中身を 初心者向けに使い方を解説する内容になります 使いたいコード 置換 = List.Accumulate(   Table.ToRows(t...

さきほど完成したメインクエリに、次のようなカスタム列を追加します。

=List.Accumulate(Table.ToRows(都道府県名),[都道府県名],(x,y)=>Text.Replace(x,y{0},y{1}))
=List.Accumulate(Table.ToRows(都道府県名),[都道府県名],(x,y)=>Text.Replace(x,y{0},y{1}))

すると、このように置換されたものがカスタム列として生成されます。

和訳された都道府県名

あとは、もとの[都道府県]列を削除してととのえれば完成です。

完成した都道府県名

※行数が非常に多い場合は、動作が遅いので要注意

この一括置換はたいへん便利なのですが、行数が数十万行~数百万行のデータに対して行うと、動作が猛烈に遅くなるので注意が必要です。

行数が多い場合のベストプラクティスとして、「ステップを追加し終わるまでは、先頭の少ない行のみを保持して作業し、最後にそのステップを削除する」ことがMicrosoftによって推奨されていますが、

Power Query を使用するときのベスト プラクティス - Power Query
Power Query を使用するときのベスト プラクティス

残念ながらこの件については、この方法をとったとしても、最後の読み込み段階で動作が遅くなり、データモデルに読み込ませるために半日を要するようなことが、実際にありました。

ご注意ください。

パソコン教室・キュリオステーション志木店からのお知らせ
レッスンはオンラインで受講できます

パソコン教室・キュリオステーション志木店では、本年よりオンラインでの在宅レッスンを実施しております。
教室の全コースがオンラインで受講可能。実際にインストラクターがご対応いたします。
1時間の無料体験レッスンはいつでも予約できます。詳しくは公式ページをご覧ください。

スポンサーリンク
キュリオステーション志木店運営をフォローする
志木駅前のパソコン教室・キュリオステーション志木店のブログ

コメント

タイトルとURLをコピーしました