タグ別アーカイブ: カレンダーテーブル

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へ

DAX使用時に罠に陥り易いところ~Power Pivot、Power BI~

今回の記事では、DAXを使用していて罠に陥り易いところを解説します

DAXは登場してまだ日が浅く、あまり事例が溜まっていないこともあるので、まだDAXを始めたばかりの方は

「あれ、なんでうまくいかないんだろう・・・」

ということも多いと思います

特にリレーション関連では不可解に感じることもあるかと思います

この記事では、自身の経験も含めて「思わぬところでハマった」箇所を解説していきます

ポイントは次の5つとなります

・データ変更時の更新処理

・DAX式の()の数の確認

・事業年度の月次の並び

・1対多の関係性の確認

・Power Query内のデータ形式の確認

更新漏れ

これは物凄い単純なのですが、急いでいる時などにハマる方もいらっしゃると思いますので紹介します

例えば、上の画像のように空欄があるデータがあり、後から元データの空欄を埋めたとします(✕✕物流

ところが「Power Pivot for Excel」の画面には反映していないので「なぜだ?」というケースがありました

元のデータを変えた場合には下の画像の黄色の箇所「最新の情報に更新」にて更新処理を必ず行いましょう。すぐに元データの変更が反映されます

【)】の抜け

意外とエラーの原因が分からないのが左括弧/に対応する右括弧/)の抜けです

特に変数を使うケースなどは分かりにくいです

赤字の波線が出たら、落ち着いて「赤字の波線」がではじめたところのの組み合わせを観察しましょう

下の画像では黄色の箇所でがぬけています

事業年度の月次表示

日本では多くの事業年度は4月から翌年の3月までです

その際、IF式を使って事業年度を調整する方も多いと思います

ところが、月の表示を組み合わせると下の画像のように「1~3月」が思うような位置に並びません

上の画像の場合で言えば、本来は2021年度は4月から始まり、1~3月は最後に回って欲しいのです

この場合は、事業月を「yyyy年MM月」を表示する月を作成した方が早いです

下の画像では「FORMAT」を使用して表示を「yyyy年MM月」に変えています

これで月の表示も狙い通りになります

リレーションが効いていない

DAXを使う際に、厄介なことの一つがリレーションが効いてない時の対処です

リレーションが効いていないと、全て同じ数字になるなどの状態になります

この場合は下の画像のようにアラームは出ています

この様なときは丁寧にリレーションの状況を確認して、の漏れを見つけましょう

リレーションの線が破線になるケース

しっかり抜け漏れなくリレーションを作成しても、リレーションの線が破線になり、リレーションが有効にならないケースがあります

中にはうっかり「非アクティブ化」をクリックしてしまい、波線になっていることもあるかと思います

これはDAXを始めた頃は解決方法が分かりにくいかと思います

後、原因が分かりにくいのが次の画像のケースです

上の画像では「▶」の向きがになっており、本来「1」は予定表の方についているべきなのです

この矢印の逆向きの話しについては「1対多」の関係を考慮する必要があります

特に管理画面ではなく、2つのテーブル間でクリック処理によりリレーションを作成する場合に注意が必要です

ちなみに上の画像内のは「多」という意味です

下の画像は注文データテーブルとカレンダーテーブル(グレーの線)の組み合わせになっています

カレンダーテーブルには「2019/04/02」はつしかありません

一方、注文データテーブルには「2019/04/02」が複数存在します

ですのでカレンダーテーブルの「2019/04/02」を「2019/04/02」が多数存在する注文データテーブルの方向に紐づける必要があります

上記の説明が、少し分かりにくいと感じた方は次のケースで考えてみてください

カレンダーテーブルに多数「2019/04/02」が存在し、紐づけようがなくなってしまう場合です

上の画像のように本来は1になっているべきカレンダーテーブル側に「2019/04/02」が複数ある場合、カレンダーテーブル内のABのどちらを注文テーブル側の「2019/04/02」と紐づけたらいいか分からないですよね

つまり、紐づける方向は多ではなくからになるということです

では、具体的にリレーションの線が破線になっているのを修正したいと思います

1になるカレンダーテーブルの予定表から注文データに線を引っ張ります

こうすることで正しいリレーションを作成することができます

正しくリレーションが作成できている場合にはリレーション管理画面は次のようになっています

1の方がにありますので、この点もご注意ください

リレーションの方向が一致しているのにリレーションが有効にならないケース

Power Queryで作成したクエリをデータモデルに追加した場合には、クエリ内のデータ形式が一致せずに2つのテーブルが紐付かず、リレーションが効かないケースがあります

このケースが一番、罠にハマるパターンの中で原因に気づけないケースです

但し、データ形式を変えればあっさりリレーションは有効になります

これはAccessと違い、テーブルを構築する際にPower PivotやPower Queryの場合にはデータ形式を設定する必要がないことと深く関係があります

下の画像は、Accessの画面ですがテーブルを作成する際の画面です

こちらは、Power Queryエディタの画面です

Power Queryではデータ形式は変更できますが、基本的には自動設定です

特に日付データの場合は型式が「テキスト」でも「日付」でも見た目が一緒なので、既存のデータモデルとの型式の違いに気づきにくいです

ですので、特にカレンダーテーブルとのリレーションを作成する時にはPower Query側の日付形式にはご注意ください

<まとめ>

今回はPower PivotやPower BIでDAXを使用する時にハマりやすいものを、小さいものから大きい物まで幅広く紹介しました

ポイントをまとめると次のようになります

・データ変更時の更新処理

・DAX式の()の数の確認

・事業年度の月次の並び

・1対多の関係性の確認

・Power Query内のデータ形式の確認

ぜひ上の5つを意識しながら、DAXを活用してみてください

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

にほんブログ村

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