【Excel】PowerQueryのグループ化の単位をセルの値で変更する

何ができるのか

今回はExcelのPowerQueryでグループ化する際に、セルの値によってグループ化の単位を変更する手順を説明します。
例えば日付ごとのデータがある場合、期間が経過するとデータ量が増えてきます。Excelのシート上に展開してるとどんどんと重くなってくるので、PowerQueryでグループ化して運用したくなります。このときのグループ化の単位をセルの値をパラメーターとして読み込まれるデータ量を変化させる方法を説明します。
例えば以下のような日付情報を持つ明細データがあるとします。

この日付から、年月ごとや年ごとに集計するのをセルの値で制御することができるようになります。

STEP1．集計用の準備

まずは対象となるデータをPowerQueryで読み込みます。
ここでは同じExcelのテーブルを読み込んだ状態としていますが、他のExcelやCSVファイルから読み込んだ状態と仮定して下さい。クエリ名は【01_Tbl_売上明細】とします。

今回は年月日のデータから、[年]、[年-四半期]、[年-月]、[週]という列を追加して集計単位をこれらで行えるようにします。
まず【01_Tbl_売上明細】を参照して、クエリを作ります。

クエリ名は【02_年月日関連追加】とします。

日付列となる[年月日]を選択して、「列の追加」タブの「日付」から集計したい単位となる年月日関連の列を追加していきます。

今回は[年]、[四半期]、[月]、[週の開始日]を追加して以下の状態にします。

このままだと分かりにくいので、少し加工します。まずは[四半期]について、「列の追加」タブの「条件列」から以下のように設定します。

続いて[月]について、前0を付けて2桁にします。[月]を選択した状態で、「変換」タブの「書式」から「プレフィックスの追加」を選択します。

「0」を入力して前に0を追加します。

2桁にするために、[月]を選択した状態で、「変換」タブの「抽出」から「最後の文字」を選択します。

「2」を入力して2桁にします。

[年]と[四半期月]、[年]と[月]を結合した列を追加します。
列名を[年-四半期]とし、式に「Text.Combine({Number.ToText([年]),[四半期月]})」を設定します。

列名を[年-月]とし、式に「Text.Combine({Number.ToText([年]),"/",[月]})」を設定します。

これで今回追加したい集計単位ができました。
ただ不要な列があるので整理するために、【02_年月日関連追加】を参照して、クエリ名：【03_整形】を追加します。

[四半期]、[月]、[四半期月]を削除し、列名[週の開始日]を[週]に変更して、列を以下のように並び替えておきます。

少し長かったですが、集計のためのデータの加工は完了です。

STEP2．セルのパラメーターの設定

ここではシート上のセルの値をパラメーターにする設定を行います。まずパラメーターとなるテーブルを用意します。
今回はテーブル名を『Tbl_設定_集計期』とします。

このテーブルをデータとして取り扱います。この『Tbl_設定_集計期』にカーソルを合わせた状態で、「データ」タブの「テーブルまたは範囲から」を選択してデータの取得をします。

クエリ名は【セルParam_集計期】とします。

列の値の部分で右クリックして「ドリルダウン」を選択します。

これでパラメーターの準備ができました。
ここでパラメーターとなる集計期はプルダウンで設定できるようにしておくと便利なので、設定しておきます。単位は列名と同じになります。

STEP3．パラメーターでグループ化する

グループ化による集計用に【03_整形】を参照して、クエリ名：【11_グループ集計】を追加します。

まずは「変換」タブの「グループ化」を選択して、通常のグループ化を設定します。この例では[年]と[商品]でグループ化を設定します。

このグループ化の設定は以下のようになっています。

= Table.Group(ソース, {"年", "商品"}, {{"金額", each List.Sum([金額]), type nullable number}})

この「”年”」となっている部分をSTEP2で設定した【セルParam_集計期】で置き換えます。

= Table.Group(ソース, {セルParam_集計期, "商品"}, {{"金額", each List.Sum([金額]), type nullable number}})

すると以下のようになります。

この【11_グループ集計】をSheet上に読み込むと以下のようになります。

集計期を変更してデータ更新をしてグループ化の単位が切り替わるか確認してみます。集計期を「年-四半期」にしてデータ更新をした場合、以下のようになります。

これでいったんやりたいことができるようになりました。
ただ実務的に考えると少々問題があります。集計期を変更する度に列名が変更されるので、PivotTableを組んだりデータモデルにしていると変更の都度再設定が必要となります。
そこでもう少し工夫を加えて問題が出ないようにすることを考えます。