タグ別アーカイブ: パワークエリ
【Power Query豆知識】ちょっとしたM関数の書き方の工夫でステップ数を削減する方法
どうしてもクエリが長くなってしまい、後で見返しても内容がよくわからず困っているという声をよく聞きます
今回は、M関数の書き方を工夫してステップ数を削減する「ちょいテク」を紹介します
カスタム列の追加時
カスタム関数を追加した後に、形式を行うことがあります
下の画像では、上の画像のランク列をテキスト形式に変更しています
このやり方だと、形式変更のステップが追加されてしまいます
このケースの場合は、カスタム列を作成したステップに戻り、数式バーに一文を足します
足した一文はこちらです
,Text.Type
この一文を足すだけで、カスタム列の追加と形式変更（テキスト形式への変更）が同一ステップで行えます
では、整数形式に直す時にはどうコードを書くのか？
いちいちすべてコードを覚えておく必要はありません
下の画像は整数形式に直した時の数式バーの記述です
一度、整数形式に変更するステップを追加した後に「コードを把握」し、カスタム列の追加ステップを修正した後、形式変更のステップを削除すればいいのです
列の削除
ある列を削除した後に、別の列を削除したくなることはよくあります
この時は、最初に列を削除したステップに、2度目の内容を追加します
下の画像は1度目に列を削除した時の数式バーの内容です
こちらの波括弧：{}内に2度目に削除した列名を追加します
これで、2度目に列を削除したステップは不要となります
列名に空欄が入っている時は注意が必要です
この時は列名をコピーした方が早いです
＜まとめ＞
今回は、ちょっとした長いクエリを短くする工夫を紹介しました
今回の内容で一気にステップ数が減るわけではありません
ただ地道に工夫を積み上げると成果には確実につながりますので、ぜひ今回のような工夫を積み上げるようにしましょう
UDEMYの動画コース「Power Queryを極めたい人必見！！M関数講座」の無料・勉強会の開催報告
昨日、リリース済みのUdemy動画コース「Power Queryを極めたい人必見！！M関数講座」の無料勉強会を開催しました
参加して頂いた皆様には感謝の一言です！
今後の動画コースの内容、並びに、Power Queryの技術改善に多くの示唆を得ることができました！
今回の勉強会では2点「let式を使用した複数ステップの集約」「クエリの関数化」に絞ってお話させて頂きました
長いクエリの対策
Power Queryは便利なので、Power Queryに慣れてくるとどうしても適用したステップが長くなります
ステップが長くなると、後で見返してもどこがどうなっているのかわからなくなったりします
この点が、皆でPower Queryを共有して使う時にも問題となります
今回、日常的に業務でPower Queryを使用している参加者の皆さんと議論させて頂き「長いクエリ」への対策が必要なことがよく認識できました
そして「let式を使用した複数ステップの集約」の技術が長いクエリへの対策に役立つことも、参加者の皆さんに意見や感想をお聞きしてよく認識できました
下の画像のように、カスタム列作成画面でlet式を使用すれば複数ステップを１つのステップに集約することができます
しかも、コメントなども入れれば処理が複数ステップに分かれている場合よりもステップの意図がわかりやすくなります
let式の文法に慣れる必要がありますが、この方式を積極的に使えば、長くクエリをシンプルにわかりやすくできます
クエリの関数化
「販売単価の列」「生存原価の列」と「販売個数の列」を乗算し「利益を計算する列」を作成するなどのカステム関数はこのブログでも紹介しました
今回解説したのは、クエリ自体を関数化する方法です
通常のカスタム関数では各行にて１つの値（販売単価、製造原価、販売個数⇒利益）が出力されますが、クエリの関数化の場合には値の代わりにテーブルが算出されます
今回紹介したのは、ファイル内のテーブルを取得するクエリの関数化ですが、思った以上に使い道がありそうです
複数ファイルを取得するのは、フォルダ内の複数ファイルを取得する方法でも行えます
参加者からおききしたのですが、この方法だとクエリが重くなり、フォルダからファイルを幾つか抜き出すケースもあるそうです
確かにフォルダ内のファイル一括取得は便利ですが、日常業務では確かに頻発しそうな問題だと感じました
今回紹介したデータ取得するクエリの関数化ではフォルダ内のファイルを細分化して取得することができるので、クエリが重くなる可能性を低くできます
データ取得以外にもクエリの関数化の用途がありそうなので、今後も積極的に研究していきたいと思います
＜まとめ＞
今回は本当に貴重な意見をいくつもお聞きすることができました
今後もユーザーの方との交流しながら、自分自身のスキルアップを図っていきたいと思います
Udemyの動画も頂いた指摘を基にして修正を行っていきたいと思います
⇒Udemyの動画・詳細はこちらから
（続編）２つの表にてどの行が追加、削除されている？ ⇒どの列が変更になっている？
前回の続きで、パワークエリを使用して行だけでなく列についても、値の変更もあわせて変更箇所を特定できるようにしたいと思います
目次
ポイント
２つの表を結合するのは前回と一緒です
今回はピボット解除を行い、複合キーを作ったうえで結合を行います
使うデータ
Before、Afterの２つの表を用意するのですが、キー（りんご、みかん・・）を行単位で作成し、列は月単位で作成します
最後は２つの表でどこが変わったかを明確にできるようにクエリを作成します
ピボット解除＆複合キー作成
BefoerとAfter、それぞれのクエリはピボット解除を行い、縦横並びを縦縦並びに変えます
その後、「列のマージ」を使用して、複合キーを作成します
この複合キーを使用して、BeforeとAfterの２つの表を結合（マージ）します
マージ処理
ポイントでも前述したように、２つの表を複合キーを使用してマージ処理します
マージ種類は通常でいけば、完全結合がよいです
「完全外部」の結合であればAfterの表にて追加・削除されたものが全て網羅できます
但し、こちらは用途にあわせて６つの結合種類を使いわければよいかと思います
各種調整
判定列の追加
マージ後は、条件列を使用して〇×を判定する列を追加することができます
複合キーの分解
マージ後は複合キーを分解して必要な情報だけ抜きだすこともできます
複合キーの分解は「抽出」の「区切り記号の後のテキスト」を使用し、複合キーから「月」を抜き出します
そうすると、BeforeとAfterの比較が行いやすくなります
＜まとめ＞
今回はマージを更に有効活用して、行だけでなく、変更があった列と値を特定できるようにしました
ポイントはピボット解除と複合キーの作成により、２つの表を比較しやすい形で結合することです
ぜひ有効活用してみてください
これまで受けてきた質問と回答～Power BIレッスン日記～
Power BIはエクセルと比べると歴史が浅く、まだ知見が溜まりきっていません
私自身のためにもこれまで受けてきた質問と回答についてまとめていきます
スライサー文字列検索
質問
通常のスライサーにて文字列で検索すると、いちいち表示されたものをクリック・指定するのがとても大変です
回答
「その他のビジュアルの取得」から文字列検索用のスライサーを追加します
「Text Filter」が文字列検索用のスライサーです
「Text Filter」で文字列検索が行えます
複数のグラフの軸の範囲を常に同じにしたい
質問
複数のグラフを同じ基準で比較できるように、データの中身が変更になっても最大値と最小値を’常に’グラフ間で同じ基準にて設定できるようにしたい
回答
下の画像の関数マークから「変数」のような設定の仕方が可能です
適したフィールドの最小値や最大値で設定することができます
場合によっては、専用のテーブルを作成して「フィールドの最小値ｘ1.3」などの調整も可能です
グラフのデータラベルの表示
質問
データラベルが重なり合って表示されません
回答
この場合はグラフを縦に延ばせば、表示されますが、常にこの方法を行うわけにはいきません
この場合は文字の向きを縦にする方法もあります
ビジュアルの書式設定の「データラベル」⇒「オプション」から設定は変えられます
取り込んだデータが表示されない
質問
複数シートから構成されるファイルのデータを取り込んでも、一部のシートのデータが「データ管理画面」に表示されない
ただ、エディタ画面には表示される
回答
この場合には、エクセルファイル内で事前にテーブル化を行っておくことをおすすめします
yyyyMM形式のカレンダーテーブルでの活用
質問
201901などの形式しかデータにない時があります
この場合は、そのままではカレンダーテーブルとリレーションが作成できません
回答
一番簡単なのは、データの変換（パワークエリエディタ）画面にて年と月の列に一旦分け、#date関数によりyyyy/MM/01に変える方法です
・年⇒列の追加タブ⇒抽出⇒最初の文字
・月⇒列の追加タブ⇒抽出⇒範囲
その後、カスタム列作成画面で#date関数を使い、「2019/01/01」に変えます
⇒#date(年,月,1)
＊データ形式は日付形式
累計の計算
質問
累計の計算はどう行いますでしょうか？
回答
DAXで下記のようなメジャーを作成します
累計 = TOTALYTD(SUM(‘売上データ'[売上金額]),’Calendar'[Date],”3/31″)
*会計年度が４月～翌３月の前提
空欄の表示
質問
ピボット解除をした時に、空欄の箇所が表示されない
元データは９月（G列）まであるのに、６月までしか表示されない
回答
ピボット解除のステップ前に、空欄/null⇒０への置き換えが必要です
値の置き換えを行う前に、事前に列の形式は「整数」に変えておく必要があります
理由はテキスト形式などだと「０」への置き換えができないということです
ただ、もう一工夫が必要です
上記のように７月～が日付形式になっています
何故なら、nullがあると他の列と違い、自動的に７月～の列が日付形式になり、７月１日に自動変換されるからです
ですので、更に形式をテキスト形式にしておく必要があります
２つの表にてどの行が追加、削除されている？～結合の6種類～
２つの表の間でどの行が追加されているのか？削除されているのか？を適時確認・集計できるととても便利です
受注のキャンセルデータの管理やミスの発見など、2つの表の適時確認・集計の活用の場はかなり広いです
Power Queryの結合（マージ）には6種類が用意されていますので、それぞれを使い分けるとかなりの集計上手になれます！
今回使用するデータ
今回は１から始まる連番で管理された２つの表を使用します
結合（マージ）
結合の種類は下の画像の赤色の箇所で選択・指定できます
左外部
左の表の全てと、右の表にて左の行と一致する行を結合します
右外部
右の表の全てと、左の表にて右の行と一致する行を結合します
完全外部
一致する一致しないに関わらず、２つの表に存在する行を網羅します
右の表で追加された行や削除された行が一目でわかるようになります
内部
２つの表の間で一致する行のみ結合します
左反
左の表から右の行と一致しないものを抽出します
右の表にて削除された行が一目でわかるようになります
右反
右の表から左の表の行と一致しないものを抽出します
右の表で追加された行が一目でわかります
＜まとめ＞
Power Queryの結合にて一つ問題になるのは、表現がわかりにくいことです
「最初の行」とは左にある表、「2番目の行」とは右にある表と置き換えればわかりやすいと思います
複数グループ内で値違いの箇所を見つける～グループ化の応用～
先日ある方から大量の商品データの中から、価格違いの設定が起こっているところを見つけたいという依頼がありました
またルールがあり、同じ素材/同グループであれば販売する色種類が違っていても同じ価格で設定しなければいけないということでした
もちろん、目でみて判断することもできますが、大量データがある場合にはとても困難です
こういった場合はPower Queryのグループ化機能で簡単に価格違いが起こっているグループを見つけることができます
今回は次のデータを使って解説を行いたいと思います
ある商品を価格設定するときに、地区内では同じ価格設定にしなければならないものとします
こちらのデータから価格違いが起こっている地区を見つけます
ポイント
Power Queryのグループ化機能では、グループ化の方法として「最小」「最大」がありますので、こちらを活用します
グループ化の適用
解説はPower Queryエディターからはじめさせて頂きます
ちなみに、上の画像では元のエクセルデータにフィルを適用していますので空欄が埋まっています
上記の画像の状態からまずグループ化をクリックします
デフォルトでは「基本」設定になっていますが、今回は「詳細設定」を指定します
こうすることで、「集計の追加」ボタンにより、グループ化の集計方法を「最小」に加えて「最大」も指定することができます
グループ化を実際に適用すると下の画像のような状態になります
グループ内にもし価格違いがあると、上の画像の黄色の箇所のように「最小:1400＜＞最大:1500」となります
条件列の作成
ここからは条件列を作成して、最小と最大が違うときは「X」そうでなければ「〇」という表示をする列を作成します
条件列は次のように設定します
価格違いがあるグループと明細リストの抽出
上の条件列を作成すると下の画像のような状態になっています
もちろん、「×」の箇所だけフィルターをすれば、グループ内で価格違いが起こっている箇所は分かります
ただ、下の画像のように店舗と価格もリストにしたいとします
目で元のデータをフィルターする方法もありますが、M関数を使い、自動的にフィルターする方法もあります
まずは下の画像の状態で、適用したステップを「myList」とするところから解説を始めたいと思います
後でこちらの「myList」を参照します
次に
関数マーク/fx*をクリックしたステップを追加した後に、元の「フィルした状態/下方向へコピー済みステップ」を参照します
＊参照ステップの記事を参照
これで、グループ化を行う前の状態に戻りました
ここから「地区」列がmyListに含まれるかどうかを判定する列を「カスタム列」作成画面で作成します
List.Contains関数を使うことで、地区の列の値が「myList」ステップの地区列に含まれるかどうかを判定できます
ここから「TRUE」のみをフィルターすればリストは完成です
＜まとめ＞
今回は、グループ化機能の最小と最大をうまく組み合わせて、グループ内の違う価格設定を発見する方法を紹介しました
ピボットテーブルでも同じことができます
ピボットテーブルの場合には分散や標準偏差を出す方法もあります
グループ内で価格にバラツキがある場合には、分散や標準偏差が０以外になります
【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法
【列方向ではフィルターは行えないと思っていませんか？実は3クリックをプラスすれば可能です】
Power Queryエディタ画面にて、空欄だけの列は処理したくない・と思ったことはありませんか？
実は「ヘッダーの上げ下げ」と「行列の入れ替え」を組み合わせれば可能です
上記のGIF画像では、空欄になっている「２」「４」の列をフィルターしています
ポイント
一番重要なのは2回にわたる「行列の入れ替え」です
行列を入れ替えれば、列方向のデータを行方向に変換できます
フィルター処理をした後は再度、行列を入れ替えます
処理の実際
行列を入れ替える前にまずは「ヘッダー行」を下げておきます
ヘッダー行を下げておくことで、見出しも2回にわたる行列入れ替え操作の対象となります
行列を入れ替えた後にフィルター処理により空欄行を除きます
フィルター処理をした後は、再度行列を入れ替えます
そのままだと、ヘッダーに本来配置されるべき列名が反映されていません
ですので、1行目をヘッダーに上げます
そうすれば完成です
＜まとめ＞
一見、列方向ではフィルター処理ができないように思えますが「行列入れ替え」処理を組み合わせれば実は簡単です
列フィルターが行えるだけで特殊なデータ整理が行えるようになります
今回紹介したのは空欄のフィルタでしたが、列名の置き換えなども一括で行えます
ぜひ実際に手を動かして試してみてください
最後に、ヘッダーの上げ下げを組み合わせることだけは忘れないようにしましょう！
Power Queryならではのタイムテーブル活用方法
Power BIやPower Pivotではタイムテーブルの活用が欠かせません。ただPower Queryでもタイムターブルの作成と活用を行うことができます。今回の記事ではPower Queryならではのタイムテーブルの活用方法を紹介します
タイムテーブルを活用することで、集計作業で必ず必要になる「前期比での対比」「四半期単位への変換」などを簡単に行えるようになります
ボタンのワンクリック、もしくはたった1行の簡単なコード入力だけで、連続した日付が網羅されたカレンダーテーブルを作成できるのは本当に便利です
Power Queryでも空のクエリから1行のコードを入力するだけで、全く同じようにカレンダーテーブルを作成できます
今回は作成したカレンダーテーブルを活用して、担当者のスケジュール表を作成してみましょう
ポイント
別テーブル作成
カレンダーテーブルを作成するのはもちろんですが、カレンダーテーブルとは別に担当者テーブルを作成してマージします
担当者テーブルを作成する時には、マージする際のキーにする列「１」を作成するのもポイントです
もちろんカレンダーテーブル側にも同じ内容のキー列を準備しておく必要があります
ピボット
カレンダーテーブルは当然、下の画像のように縦方向で作成されます
こちらはピボット処理により縦⇒横に方向を変えます
上の画像の各日付の列には空欄が入力されています
この点については詳細を後述します
カレンダーテーブルの作成
Power Queryのカレンダーテーブルは、空のクエリから波括弧:{}と複数のM関数を組み合わせて作成します
・波括弧：{}⇒..と組み合わせて連続データを作成
・#date関数⇒日付データを作成
・Number.From⇒数字データを作成
上記の３つを組み合わせてカレンダーリストを作成します
= {Number.From(#date(2022,4,1))..Number.From(#date(2022,4,30))}
リストを作成したらテーブルに変換します
更に日付形式に変換します
担当者クエリの作成
こちらはポイントで前述したように、キー列を設定したうえで作成します
担当者の人数が少なければ、Power Queryエディタ上からの入力も可能です
カレンダーテーブルのピボット処理
前述のカレンダーテーブルには、ピボット処理（縦⇒横）の前に２つの列を追加しておきます
キー列の追加
カスタム列・作成画面にて担当者クエリで作成したキー列と同じ内容の列を作成します
空欄列の追加
こちらの追加処理はとても重要な処理です
この空欄列は、連続日付データをピボット（縦⇒横）に並べ替えた際に表の中身となり、スケジュール表の入力欄になるものです
空欄の作成はキー列と同じくカスタム列・作成画面で行います
ピボット処理
ピボット処理は変換タブから行います
上記の「列のピボット」をクリックする前に、連続日付データがある列をカーソルで選択しておきます
「列のピボット」をクリックしたらダイアログボックスが表示されます
こちらで２つ指定します
・値列（表の中身）⇒空欄列
・詳細設定オプション⇒値の集計関数⇒集計しない
上記のピボット処理により、下の画像のような形になります
クエリのマージ
前述の２つのクエリをマージすると、担当者別スケジュール表が完成します
ただキーの列は削除しておきましょう
＜まとめ＞
今回はPower Queryならではのカレンダーテーブルの活用方法を解説しました
カレンダーテーブルは時系列のデータを集計する上でとても便利なものです
本来はPower Queryではカレンダーテーブルの機能はありませんが、M言語の簡単な仕組みを組み合わせれば簡単に作成することができます（{}、..、#date、Number.From）
今回紹介した活用方法は「担当者スケジュール表」でしたが、他にも活用方法があるはずです
ぜひ、自分なりの活用方法を開発してみてください！
【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換
読者の皆さんも見やすくするためにセル結合を組み合わせた、下の画像のような表をみたことがありますよね！こちらの表をPower Queryの基礎技術を組み合わせてテーブル形式にしましょう！
この手の表は見た目がよさそうな気がしますが、データを追加したり、編集するのにとても不便です
「この表はもう加工・編集のしようがない・・」
そんなことはありません、あくまで「ピボット解除、行列入れ替え」「フィル」「列のマージ」「列の分割」などの技術の組み合わせで簡単にテーブル形式に直せます
今回は行列ともにセル結合がある表を扱いますが、行方向のみの場合は過去にも記事を書いてますので、ぜひそちらもご参照ください ⇒過去の記事
今回のポイントは列のマージと列の分割の組み合わせです！
行列を入れ替えるのと、フィルで空欄を埋めるのは行方向のみセル結合がある場合と一緒です！
小計や合計の行列を消去
解説はPower Queryエディタの状態からはじめさせて頂きます
小計の行や合計の列は必要ないので、フィルターしたり列の削除を行い、消去してしまいましょう
下方向のフィル
一つ目のセル結合はフィルで埋めます
フィルは変換タブから行います
下の方向でフィルを行えば、下の画像のようにnull/空欄が埋まります
列の結合
後でデータを扱いやすくするために、部門と四半期の列は「列のマージ」により1列にまとめてしまいます
列のマージは変換タブで行います
列のマージ時には「－」などの記号により、後で分割しやすくしやすくしておくのがポイントです
列のマージを行ったことで、表がシンプルに整理されてきました
行列の入れ替え
この後、行列の入れ替えを行いますが、１つ準備が必要です
見出しをデータに降ろす必要があります
変換タブにで「ヘッダーを1行目として使用」をクリックします
下の画像が見出しをデータに降ろした状態です
ここから「関東・・・の行」も含めた形で行列の入れ替えを行います
行列を入れ替えると、再びフィルでセル結合を埋めることができます
条件列＆フィル
ここからフィルで一気に空欄を埋めたいところですが、よくみるとnullでなく「列・・・」で埋まってしまっています
ここは条件列を使用して、列から始まる箇所をnullに変換しておきます
これでフィルで空欄を埋めることができるようになりました
フィルで空欄を埋めると、いよいよ最終形が見えてきます
ここからは①行をヘッダーに昇格⇒②ピポット解除を行います
そして最後に列をマージした列を再度分割します
ピボット解除
ピボット解除を行う前に1行目をヘッダーにします
こちらも前回同様に変換タブから行います
これでピボット解除を行う体制になりました
ピボット解除は左2列を軸にして行います
「その他のピボット解除」を行うと最終形の一歩手前です
ここから、前述の通り、列の分割を行います
列の分割は、列のマージを行ったときに「列の間に入れた記号」を指定して行います
列の分割を行えば、これで最終形となります
後は列名などを調整しましょう！
＜まとめ＞
今回は長文にて行列双方向にセル結合がある複雑な表をテーブル形式にしました
長文ではありますが、使用している技術は基本的な内容ばかりです
ただ、一つ抜けるとすべてが狂ってしまうので、その点だけは注意頂きたいと思います
今回、記事を作成してみて感じたのは「見出し」が無い表は結局、あまり見栄えがよくはなりません
その点は日常的に意識していきたいものです