タグ別アーカイブ: Power Query

2つの表にてどの行が追加、削除されている?~結合の6種類~

2つの表の間でどの行が追加されているのか?削除されているのか?を適時確認・集計できるととても便利です

受注のキャンセルデータの管理やミスの発見など、2つの表の適時確認・集計の活用の場はかなり広いです

Power Query結合(マージ)には6種類が用意されていますので、それぞれを使い分けるとかなりの集計上手になれます!

今回使用するデータ

今回は1から始まる連番で管理された2つの表を使用します

結合(マージ)

結合の種類は下の画像の赤色の箇所で選択・指定できます

左外部

左の表の全てと、右の表にて左の行と一致する行を結合します

左外部

右外部

右の表の全てと、左の表にて右の行と一致する行を結合します

右外部

完全外部

一致する一致しないに関わらず、2つの表に存在する行を網羅します

右の表で追加された行や削除された行が一目でわかるようになります

完全外部

内部

2つの表の間で一致する行のみ結合します

内部

左反

左の表から右の行と一致しないものを抽出します

右の表にて削除された行が一目でわかるようになります

左反

右反

右の表から左の表の行と一致しないものを抽出します

右の表で追加された行が一目でわかります

右反

<まとめ>

Power Queryの結合にて一つ問題になるのは、表現がわかりにくいことです

「最初の行」とは左にある表、「2番目の行」とは右にある表と置き換えればわかりやすいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

複数グループ内で値違いの箇所を見つける~グループ化の応用~

先日ある方から大量の商品データの中から、価格違いの設定が起こっているところを見つけたいという依頼がありました

またルールがあり、同じ素材/同グループであれば販売する色種類が違っていても同じ価格で設定しなければいけないということでした

もちろん、目でみて判断することもできますが、大量データがある場合にはとても困難です

こういった場合はPower Queryグループ化機能で簡単に価格違いが起こっているグループを見つけることができます

今回は次のデータを使って解説を行いたいと思います

ある商品を価格設定するときに、地区内では同じ価格設定にしなければならないものとします

こちらのデータから価格違いが起こっている地区を見つけます

ポイント

Power Queryのグループ化機能では、グループ化の方法として「最小」「最大」がありますので、こちらを活用します

グループ化の適用

解説はPower Queryエディターからはじめさせて頂きます

ちなみに、上の画像では元のエクセルデータにフィルを適用していますので空欄が埋まっています

上記の画像の状態からまずグループ化をクリックします

デフォルトでは「基本」設定になっていますが、今回は「詳細設定」を指定します

こうすることで、「集計の追加」ボタンにより、グループ化の集計方法を「最小」に加えて「最大」も指定することができます

グループ化を実際に適用すると下の画像のような状態になります

グループ内にもし価格違いがあると、上の画像の黄色の箇所のように「最小:1400<>最大:1500」となります

条件列の作成

ここからは条件列を作成して、最小と最大が違うときは「X」そうでなければ「」という表示をする列を作成します

条件列は次のように設定します

価格違いがあるグループと明細リストの抽出

上の条件列を作成すると下の画像のような状態になっています

もちろん、「×」の箇所だけフィルターをすれば、グループ内で価格違いが起こっている箇所は分かります

ただ、下の画像のように店舗と価格もリストにしたいとします

目で元のデータをフィルターする方法もありますが、M関数を使い、自動的にフィルターする方法もあります

まずは下の画像の状態で、適用したステップを「myList」とするところから解説を始めたいと思います

後でこちらの「myList」を参照します

次に

関数マーク/fx*をクリックしたステップを追加した後に、元の「フィルした状態/下方向へコピー済みステップ」を参照します

*参照ステップの記事を参照

これで、グループ化を行う前の状態に戻りました

ここから「地区」列がmyListに含まれるかどうかを判定する列を「カスタム列」作成画面で作成します

List.Contains関数を使うことで、地区の列の値が「myList」ステップの地区列に含まれるかどうかを判定できます

ここから「TRUE」のみをフィルターすればリストは完成です

<まとめ>

今回は、グループ化機能の最小と最大をうまく組み合わせて、グループ内の違う価格設定を発見する方法を紹介しました

ピボットテーブルでも同じことができます

ピボットテーブルの場合には分散や標準偏差を出す方法もあります

グループ内で価格にバラツキがある場合には、分散や標準偏差が0以外になります

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法

【列方向ではフィルターは行えないと思っていませんか?実は3クリックをプラスすれば可能です】

Power Queryエディタ画面にて、空欄だけの列は処理したくない・と思ったことはありませんか?

実は「ヘッダーの上げ下げ」と「行列の入れ替え」を組み合わせれば可能です

上記のGIF画像では、空欄になっている「」「」の列をフィルターしています

ポイント

一番重要なのは2回にわたる「行列の入れ替え」です

行列を入れ替えれば、列方向のデータを行方向に変換できます

フィルター処理をした後は再度、行列を入れ替えます

処理の実際

行列を入れ替える前にまずは「ヘッダー行」を下げておきます

ヘッダー行を下げておくことで、見出しも2回にわたる行列入れ替え操作の対象となります

行列を入れ替えた後にフィルター処理により空欄行を除きます

フィルター処理をした後は、再度行列を入れ替えます

そのままだと、ヘッダーに本来配置されるべき列名が反映されていません

ですので、1行目をヘッダーに上げます

そうすれば完成です

<まとめ>

一見、列方向ではフィルター処理ができないように思えますが「行列入れ替え」処理を組み合わせれば実は簡単です

列フィルターが行えるだけで特殊なデータ整理が行えるようになります

今回紹介したのは空欄のフィルタでしたが、列名の置き換えなども一括で行えます

ぜひ実際に手を動かして試してみてください

最後に、ヘッダーの上げ下げを組み合わせることだけは忘れないようにしましょう!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

Power Queryならではのタイムテーブル活用方法

Power BIやPower Pivotではタイムテーブルの活用が欠かせません。ただPower Queryでもタイムターブルの作成と活用を行うことができます。今回の記事ではPower Queryならではのタイムテーブルの活用方法を紹介します

タイムテーブルを活用することで、集計作業で必ず必要になる「前期比での対比」「四半期単位への変換」などを簡単に行えるようになります

ボタンのワンクリック、もしくはたった1行の簡単なコード入力だけで、連続した日付が網羅されたカレンダーテーブルを作成できるのは本当に便利です

Power Queryでも空のクエリから1行のコードを入力するだけで、全く同じようにカレンダーテーブルを作成できます

今回は作成したカレンダーテーブルを活用して、担当者のスケジュール表を作成してみましょう

ポイント

別テーブル作成

カレンダーテーブルを作成するのはもちろんですが、カレンダーテーブルとは別に担当者テーブルを作成してマージします

担当者テーブルを作成する時には、マージする際のキーにする列「」を作成するのもポイントです

もちろんカレンダーテーブル側にも同じ内容のキー列を準備しておく必要があります

ピボット

カレンダーテーブルは当然、下の画像のように縦方向で作成されます

こちらはピボット処理によりに方向を変えます

上の画像の各日付の列には空欄が入力されています

この点については詳細を後述します

カレンダーテーブルの作成

Power Queryのカレンダーテーブルは、空のクエリから波括弧:{}と複数のM関数を組み合わせて作成します

・波括弧:{}⇒..と組み合わせて連続データを作成

#date関数⇒日付データを作成

Number.From⇒数字データを作成

上記の3つを組み合わせてカレンダーリストを作成します

= {Number.From(#date(2022,4,1))..Number.From(#date(2022,4,30))}

リストを作成したらテーブルに変換します

更に日付形式に変換します

担当者クエリの作成

こちらはポイントで前述したように、キー列を設定したうえで作成します

担当者の人数が少なければ、Power Queryエディタ上からの入力も可能です

カレンダーテーブルのピボット処理

前述のカレンダーテーブルには、ピボット処理(縦⇒横)の前に2つの列を追加しておきます

キー列の追加

カスタム列・作成画面にて担当者クエリで作成したキー列と同じ内容の列を作成します

空欄列の追加

こちらの追加処理はとても重要な処理です

この空欄列は、連続日付データをピボット()に並べ替えた際に表の中身となり、スケジュール表の入力欄になるものです

空欄の作成はキー列と同じくカスタム列・作成画面で行います

ピボット処理

ピボット処理は変換タブから行います

上記の「列のピボット」をクリックする前に、連続日付データがある列をカーソルで選択しておきます

「列のピボット」をクリックしたらダイアログボックスが表示されます

こちらで2つ指定します

・値列(表の中身)⇒空欄列

・詳細設定オプション⇒値の集計関数⇒集計しない

上記のピボット処理により、下の画像のような形になります

クエリのマージ

前述の2つのクエリをマージすると、担当者別スケジュール表が完成します

ただキーの列は削除しておきましょう

<まとめ>

今回はPower Queryならではのカレンダーテーブルの活用方法を解説しました

カレンダーテーブルは時系列のデータを集計する上でとても便利なものです

本来はPower Queryではカレンダーテーブルの機能はありませんが、M言語の簡単な仕組みを組み合わせれば簡単に作成することができます({}、..、#date、Number.From)

今回紹介した活用方法は「担当者スケジュール表」でしたが、他にも活用方法があるはずです

ぜひ、自分なりの活用方法を開発してみてください!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換

読者の皆さんも見やすくするためにセル結合を組み合わせた、下の画像のような表をみたことがありますよね!こちらの表をPower Queryの基礎技術を組み合わせてテーブル形式にしましょう!

この手の表は見た目がよさそうな気がしますが、データを追加したり、編集するのにとても不便です

「この表はもう加工・編集のしようがない・・」

そんなことはありません、あくまで「ピボット解除、行列入れ替え」「フィル」「列のマージ」「列の分割」などの技術の組み合わせで簡単にテーブル形式に直せます

今回は行列ともにセル結合がある表を扱いますが、行方向のみの場合は過去にも記事を書いてますので、ぜひそちらもご参照ください ⇒過去の記事

今回のポイントは列のマージと列の分割の組み合わせです!

行列を入れ替えるのと、フィルで空欄を埋めるのは行方向のみセル結合がある場合と一緒です!

小計や合計の行列を消去

解説はPower Queryエディタの状態からはじめさせて頂きます

小計の行や合計の列は必要ないので、フィルターしたり列の削除を行い、消去してしまいましょう

下方向のフィル

一つ目のセル結合はフィルで埋めます

フィルは変換タブから行います

下の方向でフィルを行えば、下の画像のようにnull/空欄が埋まります

列の結合

後でデータを扱いやすくするために、部門と四半期の列は「列のマージ」により1列にまとめてしまいます

列のマージは変換タブで行います

列のマージ時には「-」などの記号により、で分割しやすくしやすくしておくのがポイントです

列のマージを行ったことで、表がシンプルに整理されてきました

行列の入れ替え

この後、行列の入れ替えを行いますが、1つ準備が必要です

見出しをデータに降ろす必要があります

変換タブにで「ヘッダーを1行目として使用」をクリックします

下の画像が見出しをデータに降ろした状態です

ここから「関東・・・の行」も含めた形で行列の入れ替えを行います

行列を入れ替えると、再びフィルでセル結合を埋めることができます

条件列&フィル

ここからフィルで一気に空欄を埋めたいところですが、よくみるとnullでなく「列・・・」で埋まってしまっています

ここは条件列を使用して、列から始まる箇所をnullに変換しておきます

これでフィルで空欄を埋めることができるようになりました

フィルで空欄を埋めると、いよいよ最終形が見えてきます

ここからは①行をヘッダーに昇格②ピポット解除を行います

そして最後に列をマージした列を再度分割します

ピボット解除

ピボット解除を行う前に1行目をヘッダーにします

こちらも前回同様に変換タブから行います

これでピボット解除を行う体制になりました

ピボット解除は左2列を軸にして行います

「その他のピボット解除」を行うと最終形の一歩手前です

ここから、前述の通り、列の分割を行います

列の分割は、列のマージを行ったときに「列の間に入れた記号」を指定して行います

列の分割を行えば、これで最終形となります

後は列名などを調整しましょう!

<まとめ>

今回は長文にて行列双方向にセル結合がある複雑な表をテーブル形式にしました

長文ではありますが、使用している技術は基本的な内容ばかりです

ただ、一つ抜けるとすべてが狂ってしまうので、その点だけは注意頂きたいと思います

今回、記事を作成してみて感じたのは「見出し」が無い表は結局、あまり見栄えがよくはなりません

その点は日常的に意識していきたいものです

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【パワークエリ・チャレンジ】パワークエリを使用して月間スケジュール表を作成する

 今回は、開始時間・終了時間・担当者を可変で指定できる月間スケジュール表(記入用)を、Power QueryM言語の技術をフル活用して作成します

4月1日であれば、13時から15時まで1時間刻みで行が自動で用意されます

担当者も可変にできます

一番のポイントは「開始」「終了」時間の判定列を、日付(1~31)と時刻(0:00~24:00)を網羅した表に作成することです

しかも判定用の列は2列用意します

判定列を作るためには、複数のクエリを用意し、クエリ内にキー列を作成しておくこともとても重要です

キー列:yyyy/MM/dd hh:mm

内容的に大きめの内容なので、今回はポイントの羅列で失礼させて頂きますので予めご了承ください

Power Queryでここまでできるのか?という点を感じて頂けたら幸いです

今回の内容の参考記事についても事前に紹介させて頂きます

複数データの組み合わせ/クエリのマージ~中級編1回目~

見出しがセル結合により2行になってしまっている表をデータ活用1~上級編2回目~

IF式を組み合わせて列作成~上級編10回目

M言語に慣れる_4回目~特殊テンプレート作成~

【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する

変換した列の追加~中級編10回目~

ポイント

各種データの用意

今回、想定する作業は以下のスケジュール表から、必要な時間だけ記載され、担当者も記載された完成スケジュール表を作成することです

スケジュール表

下の画像が完成スケジュール表です

完成スケジュール表

実際にPower Queryのクエリを作成する前に、3つの表(日付データ、時刻データ、担当者)を用意します

日付データと時刻データについては、あとで組み合わせて、次のようなクエリ(日付時刻クエリ)を作成します

こちらの結合済みの列は後でスケジュール表とマージして「開始時間」「終了時間」を紐づけるためのキー:yyyy/MM/dd hh:mmになります

日付データ

4月のスケジュールであれば、1日から30日までを用意します

*ホームタブの連続データの作成を活用するとデータの用意が早いです

時刻データ

どの月かは別にして0:00~24:00までを用意しておきます

こちらは固定になります

担当者

こちらも用意します

日付時刻クエリとスケジュール表クエリのマージ

前述のように日付と時刻を組み合わせて作成した日付時刻クエリはスケジュール表クエリとマージして、開始時刻と終了時刻を紐づけます

マージするためのキーはyyyy/MM/dd hh:mmになります

スケジュール表をマージするためにも、事前にスケジュール表はピボット解除を行っておきます

そして日付と時刻の列はマージして「yyyy/MM/dd hh:mm」のキー列を作成しておきます

フィル機能の活用

キー列をもとにして、2つのクエリをマージするとキー列に「開始時間」「終了時間」を紐づけることができます

但し、開始時間と終了時間を判定するには、判定列が1列だけでは不十分です

1列だけだと、終了⇒開始(上の画像だと16時以降)までの時間帯が判定できないからです

フィル機能を使って、下に埋めた列を作成します

2列あれば、残すべき行を特定できます

IF式

判定列を利用して、残すべき行を判定します

判定2が開始の場合には行を残します。判定1と2が終了の場合にも残します

*1の場合は行を残す、0の場合は行を削除する

逆にそれ以外は行を残しません

担当者の紐づけ

担当者はカスタム列を作成してリストで紐づけを行います

*担当者はクエリ名です

リストで紐づけを行い、展開した状態が下の画像の状態です

ピボット処理

担当者の列の内容は、下の画像のように見出しにします

この表の値欄は空欄になるので、ダミーの列を事前に追加しておくのも大きなポイントです

その後にピボット処理を行います

最後に

以上が今回のポイントなのですが、作成していくと、ところどころで並び順が変わってしまうと思います

その際には、インデックス列を追加して調整を図ったください

今回は長文に最後までお付き合い頂き誠にありがとうございました

参考までに今回のサンプルファイルを添付します

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

IF 式の中でOR条件とAND条件を使う方法~Power Query~

IF式の中にOR条件とAND条件を組み入れて、条件式をうまく構築しよう】

Power Queryのカスタム列ではIF式を活用することができます

過去の記事でも紹介しましたが、IF式を複数ネストすることもできます

今回の記事では、IF式の条件式にAND条件とOR条件を使用する方法を紹介します

ポイントは何気にANDORの大文字と小文字の区別です

OR条件

下の画像には「A,B,C,D,E,F,G」のいずれかが含まれた文字列を含む列があります

「A」または「B」の文字列を含む場合には「true」をIF式で出力してみましょう!

If式の中にはText.Contains関数を使用します

このM関数は指定文字が含まれている場合には「true」を出力します

以下が今回のIF式です

if (Text.Contains([文字列],”A”)=true) or (Text.Contains([文字列],”B”)) then true else false

OR条件は小文字の「or」で指定する点は注意が必要です

AND条件

今度はAND条件にて「A」且つ「B」を含む場合には「true」を出力しましょう

今回もAND条件の指定を小文字にする点は注意です

if (Text.Contains([文字列],”A”)) and (Text.Contains([文字列],”B”)) then true else false

これでAとBを両方含む1行目だけがtrueと出力されました

<まとめ>

今回はPower QueryのIF式内にAND条件とOR条件を指定する方法を解説しました

なるべくIF式はすっきり記述した方がいいので、()を入れて条件式を明確にする工夫などを行って行きましょう

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【Power Query裏技】フォルダにある複数ファイルから特定のシートのみを可変で取得する方法

【Power Queryのパラメーターの仕組みを使い、複数のファイルから特定のシートのデータを可変で取得しよう】

Power Queryのフォルダにある複数ファイルから一括でデータを取得する仕組みは驚くほど便利です

この仕組みで大量の転記処理から解放された人も多いのではないでしょうか?

ただこの仕組みには一つだけ問題があります

ファイル内に複数シートがある場合や、シート名が変更になる場合です

今回の記事では、必要に応じてファイル内で取得するシート名を変更できる方法を解説します

今回使用するデータ

今回使用するファイルには次の画像のように複数のシートが入力されています

このような複数シートがある複数ファイルを一つのフォルダに格納しておきます

こちらのフォルダを指定して複数ファイルのデータを一括で取得します

ポイント

パラメーター

今回は過去の記事で解説したパラメーターを使用してシート名を指定します

今回は上の画像にある「sheetName」というパラメーターを作成し、こちらでシート名を可変で指定できるようにします

ダミーの値によるフィルター

こちらも過去記事で紹介した方法です

簡単に可変の値でフィルターをかける方法です

一度、ダミーの値でフィルターをします

その後、前述のようにフィルターをかけた値に可変にするパラメーターを組み入れます

不必要な列の削除、および行のフィルター

今回の処理では、不要な列の削除と行のフィルターを行う箇所が随所にでてきます

フォルダからデータ取得

従来の「フォルダからデータを取得してエディタを開くフロー」と一部違う点があります

今回は上の画像の箇所ではなく、次の箇所から直接エディタを開き、シート選択のステップは省略します

データ成型

エディタを開いて「適用したステップ」を確認すると、次の画像のようにステップが1つしかありません

エディタの中味はというと、次の画像のようにまだファイル内のデータを取得していない状態になっています

こここから、カスタム列作成画面でM関数を使用してデータを成型していきます

その前に、不要なファイルや列を削除していきます

~で始まるファイルはフィルター条件を指定して除いておきます

その後、「Content」「Name」以外の列を削除します

不要なファイルや列を削除したら、カスタム列作成画面でM関数を使用します

M関数:Excel.Workbookでファイル内のデータを取得できるようになります

次の画像が上記の「M関数を含むカスタム列」を挿入した後の画面です

ファイル内のデータを取得した内容を展開できるようになっています

こちらを展開処理します

ただ1回展開処理しても、まだ次の画像のようにデータ成型したと言える状態になっていません

ここから再び不要な列を削除した後、データ/Data列を展開処理します

ここから、データの1行目をヘッダーにします

その後は不必要な列は削除し、不必要な行はフィルターしておきましょう

*シート名の列は残しておきましょう

パラメーターの作成及びシート名でのフィルター処理

ここからはポイントの項で前述したパラメーターを作成します

上の画像の「新しいパラメーター」をクリックして次の画像の画面を開きます

現在の値には取得したいシート名を設定します

これでシート名を可変にするパラメーターは作成できました

その後、ダミーの値でシートをフィルターします

ここで数式バーに注目してください

ダミーでフィルターした値が数式の中に組み込まれています

こちらを設定したパラメーターで置き換えます

フィルターされたシートがData_202202➡Data_202201に変わりました

これで処理は終了です

試しにパラメーターの設定値を他の値に変えてみます

パラメーターの値に合わせてフィルターされるシート名が変更になっています

<まとめ>

今回は複数ファイルの中にある複数シートの中から、特定のシートのデータのみを一括で取得する方法を解説しました

パラメーターとダミーでフィルターを行う仕組みを組み合わせると、意外と簡単にシート名を可変で取得する仕組みが構築できます

ただ、無駄な列の削除や不要な行のフィルター処理が多い点が難点です

この点さえクリアーすれば、一度作成したパラメーターを何度でも使い回せますので、シートの管理がとても楽になります

ぜひ試してみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する

【割り算の余りの算出とピボット機能をうまく使ってVBAと同等の処理を行ってみましょう】

今回は下の画像のように1列のデータから、画像右側のようなテーブルデータを作成してみます

ポイント

ポイントは1列のデータの中で、何行目を何列目にするのかを明確にすることです

「何行目を何列目」にするかが分かれば、実はピボット機能(ピボット解除の逆)で簡単にテーブル化が行えます

インデックス列の追加と余りの計算

解説はエディタからはじめさせて頂きます

1列のデータをテーブル化してエディタを開いたら、空白をフィルターで取り除いた後に、下の画像のようにインデックス列を追加します

その後に「何行目を何列目にするか?」の印を作るために「インデックス列」を3で割り、余りを算出します

上の画像にて黄色の箇所をクリックした後、開いた画面で3を指定します

すると次の画像のような列が追加されます

引き算の追加

このままピボットをすると次の画像のようになります

空欄が混じってしまい、テーブルデータとしての規則性がありません

ですので、上の画像の「インデックス列」の代わりに3行毎集約するキーが必要になります

今回は、インデックス列から剰余の列を引くことで、下の画像のように集約キーを作成します

この引き算の処理により山田さんから始まる3行はに、木村さんから始まる行はに集約されます

引き算は余りの計算と同じ「標準」で行います

ピボット処理

では集約キーができたので、ピボット処理にて剰余の列の内容を列名になるようにします

このピボット処理は、「剰余列」を選択した上で、変換タブから行います

 列のピボットをクリックしたら、値列は列1の内容になるように指定してください

 そして、「詳細設定オプションにて」上の画像のように「集計しない」を指定してください

これで列名を変更して、無駄な列を削除したら完成です

<まとめ>

今回は、意外な方法で1列のデータから複数列のテーブルデータを作成しました

改めてPower Queryのピボットなどの機能は素晴らしいと感じました

 今回のケースは特殊であり、実際の使用機会はあまり想像がつかないですが、データ整形の際には断片的に活用できる機会も多いかと思います

ぜひ一度チャレンジしてみてください

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

【M言語実践】グループ別に累計を集計する方法

過去2回の記事でM言語を実践的に活用する事例を紹介しました

この2回の記事で共通するのは「グループ毎に連番を付与」する技術を使用していることです

今回も「グループ毎に連番を付与」する技術を有効活用して、グループ毎に累計を集計します

M関数のList.FirstNと連番をうまく組み合わせれば、意外と簡単に行えます

ポイント

List.FirstNとは?

List.FirstNは指定されたリストから、指定した条件のリストを作成します

文法としては「=List.FirstN(リスト,条件)」と書きます

例えば、

=List.FirstN(リスト,1)とした場合は1個の値が含まれるリストを作成します

=List.FirstN(リスト,2)とした場合は2個の値が含まれるリストを作成します

このList.FirstN関数の第二引数の条件のところに、グループ別の連番を指定することで、累計の元となるリストを作成します

元データ

今回解説に使用する元データは、下の画像のデータです

こちらのデータのグループに、エディタ内で連番をまずは付与します

上記の画像のようにM関数/Table.AddIndexColumnの第一引数にした列/テーブルは、後でList.FirstN関数の第一引数にしますので削除せずに残しておいてください

List.FirstNによるリスト作成

元データに連番を付与したところで、Power Queryエディタ(以降、エディタ)上で、詳細/テーブルの受注金額をM関数の第一引数、連番を第二引数にしてカスタマイズ列を作成してみます

するときちんとリストが作成できています

では、作成されたリストの中味を見てみます

連番(東京)が1の時には、作成した行の値のみが含まれています

では、連番(東京)が2の時はどうでしょう?

1つ上の行の値と該当行の値がリストに含まれています

今度は、大阪の連番1の場合を見てみましょう!

きちんと大阪の1番目の値/1700000のみが含まれています

作成されたリストの合計

List.FirstNにて各行に作成されたリストは累計の元になるリストになります

最後の仕上げとして、こちらのリストをM関数/List.Sumで合計します

すると、下の画像のように累計が算出されます

<まとめ>

 今回は、M関数/List.FirstNとグループ別の連番を組み合わせて、グループ別の累計を集計しました

今回の集計には、行別のリストが度々登場します

画像に alt 属性が指定されていません。ファイル名: image-74.png

この辺りは、手を動かして直感的に理解していった方が習得が早いと思います

とにかく手を動かして身に付けていきましょう!
にほんブログ村