タグ別アーカイブ: Mコード

M言語

【Power Query豆知識】ちょっとしたM関数の書き方の工夫でステップ数を削減する方法

2022年8月15日 analytic-vba コメントをどうぞ

どうしてもクエリが長くなってしまい、後で見返しても内容がよくわからず困っているという声をよく聞きます

今回は、M関数の書き方を工夫してステップ数を削減する「ちょいテク」を紹介します

カスタム列の追加時

カスタム関数を追加した後に、形式を行うことがあります

下の画像では、上の画像のランク列をテキスト形式に変更しています

このやり方だと、形式変更のステップが追加されてしまいます

このケースの場合は、カスタム列を作成したステップに戻り、数式バーに一文を足します

足した一文はこちらです

,Text.Type

この一文を足すだけで、カスタム列の追加と形式変更（テキスト形式への変更）が同一ステップで行えます

では、整数形式に直す時にはどうコードを書くのか？

いちいちすべてコードを覚えておく必要はありません

下の画像は整数形式に直した時の数式バーの記述です

一度、整数形式に変更するステップを追加した後に「コードを把握」し、カスタム列の追加ステップを修正した後、形式変更のステップを削除すればいいのです

列の削除

ある列を削除した後に、別の列を削除したくなることはよくあります

この時は、最初に列を削除したステップに、2度目の内容を追加します

下の画像は1度目に列を削除した時の数式バーの内容です

こちらの波括弧：{}内に2度目に削除した列名を追加します

これで、2度目に列を削除したステップは不要となります

列名に空欄が入っている時は注意が必要です

この時は列名をコピーした方が早いです

＜まとめ＞

今回は、ちょっとした長いクエリを短くする工夫を紹介しました

今回の内容で一気にステップ数が減るわけではありません

ただ地道に工夫を積み上げると成果には確実につながりますので、ぜひ今回のような工夫を積み上げるようにしましょう

にほんブログ村

セミナー・勉強会

UDEMYの動画コース「Power Queryを極めたい人必見！！M関数講座」の無料・勉強会の開催報告

2022年7月19日 analytic-vba コメントをどうぞ

昨日、リリース済みのUdemy動画コース「Power Queryを極めたい人必見！！M関数講座」の無料勉強会を開催しました

参加して頂いた皆様には感謝の一言です！

今後の動画コースの内容、並びに、Power Queryの技術改善に多くの示唆を得ることができました！

今回の勉強会では2点「let式を使用した複数ステップの集約」「クエリの関数化」に絞ってお話させて頂きました

長いクエリの対策

Power Queryは便利なので、Power Queryに慣れてくるとどうしても適用したステップが長くなります

ステップが長くなると、後で見返してもどこがどうなっているのかわからなくなったりします

この点が、皆でPower Queryを共有して使う時にも問題となります

今回、日常的に業務でPower Queryを使用している参加者の皆さんと議論させて頂き「長いクエリ」への対策が必要なことがよく認識できました

そして「let式を使用した複数ステップの集約」の技術が長いクエリへの対策に役立つことも、参加者の皆さんに意見や感想をお聞きしてよく認識できました

下の画像のように、カスタム列作成画面でlet式を使用すれば複数ステップを１つのステップに集約することができます

しかも、コメントなども入れれば処理が複数ステップに分かれている場合よりもステップの意図がわかりやすくなります

let式の文法に慣れる必要がありますが、この方式を積極的に使えば、長くクエリをシンプルにわかりやすくできます

クエリの関数化

「販売単価の列」「生存原価の列」と「販売個数の列」を乗算し「利益を計算する列」を作成するなどのカステム関数はこのブログでも紹介しました

今回解説したのは、クエリ自体を関数化する方法です

通常のカスタム関数では各行にて１つの値（販売単価、製造原価、販売個数⇒利益）が出力されますが、クエリの関数化の場合には値の代わりにテーブルが算出されます

今回紹介したのは、ファイル内のテーブルを取得するクエリの関数化ですが、思った以上に使い道がありそうです

複数ファイルを取得するのは、フォルダ内の複数ファイルを取得する方法でも行えます

参加者からおききしたのですが、この方法だとクエリが重くなり、フォルダからファイルを幾つか抜き出すケースもあるそうです

確かにフォルダ内のファイル一括取得は便利ですが、日常業務では確かに頻発しそうな問題だと感じました

今回紹介したデータ取得するクエリの関数化ではフォルダ内のファイルを細分化して取得することができるので、クエリが重くなる可能性を低くできます

データ取得以外にもクエリの関数化の用途がありそうなので、今後も積極的に研究していきたいと思います

＜まとめ＞

今回は本当に貴重な意見をいくつもお聞きすることができました

今後もユーザーの方との交流しながら、自分自身のスキルアップを図っていきたいと思います

Udemyの動画も頂いた指摘を基にして修正を行っていきたいと思います

⇒Udemyの動画・詳細はこちらから

にほんブログ村

M言語

M言語に慣れる_3回目～ダイナミックフィルタリング～

2021年2月28日 analytic-vba コメントをどうぞ

【M言語は難しくない！】今回は、数式バーを編集して「ダイナミックフィルタリング」を行ってみましょう！。ここで言う「ダイナミックフィルタリング」とはフィルタリングの値を固定せず、元データの追加や修正に応じてフィルタリングの値を自動で変動させていくものです。

例えば、受注金額・全体の「最高値」などは毎月、変動していくはずです

　ダイナミックフィルタリングでは、例えば「全体最高値」の半額未満だった担当者のリストを、毎月自動で抽出を行えるようにします

　今回のダイナミックフィルタリングを行うにあたっては、M言語を一から作成するといったことはしません

　あくまで、Power Queryエディタ（以降エディタ）にて、既に数式バーに記録されているM言語を修正するだけです

マクロ作成で言えば、記録マクロを修正するような内容です

記録されているM言語を修正するだけでも、かなりの内容が行えることを肌で感じて頂ければ幸いです！

今回のポイント

ステップ名変更

実は、エディタのステップ名は自分好みに修正していけます

今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、M言語の管理がとても楽になります

参照ステップ

　前々回、ステップをスキップして参照することで「参照ステップ」を作成しました

前々回は平均値を参照しましたが、今回は「最高値」を参照します

　クエリを更新する毎に、参照ステップの「最高値」も更新してフィルタリングの値とします

数式バーへの参照ステップ組み入れ

一度、数式バーにてダミー値でフィルタリングを行います

ダミーで設定をした箇所に、前述の参照ステップを組み入れます

今回使用するデータと行いたいこと

今回は次のデータを使用します

担当者別に受注金額を管理する表です

　こちらの表の中味が変更になっても、「最高受注金額」の担当者や「最高受注金額の半分未満」の担当者のリストを自動で抽出できるようにします

演習の中で、こちらの表にデータを新たに追加したりします

Mコードに慣れる3回目_演習

1 ファイル 17.64 KB

ダウンロード

今回のポイントと使用するデータを確認したところで、本格的な演習に入ります！

続きを読む →

M関数

日付時刻・関数

2021年7月3日 analytic-vba コメントをどうぞ

M関数辞典はこちらから

日付作成/年、月、日

#date

文法

３つの数字（年、月、日）から日付を作成

=#date(年,月,日)

使用方法

実際の使用例

　➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成

期間値作成

#duration

文法

日、時間、分、秒の４つの数字から期間値を作成

=#duration(日,時間,分,秒)

使用方法

実際の使用例

　➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成

日付リスト作成

List.Dates

文法

日付リストを指定回数分、指定期間単位で作成

=List.Dates(開始日,指定リスト数,期間単位)

実際の使用例

　➡【M言語に慣れる】_13回_ダイナミックに連続した日付作成

にほんブログ村

M関数

リスト系・操作関数

2021年7月3日 analytic-vba コメントをどうぞ

VBAで言えば、メソッドにあたるものです

M関数辞典はこちらから

フィルター作業

List.Select

文法

リストの中から条件に一致するものを抽出

=List.Selct(リスト,条件)

実際の使用例

　➡M言語に慣れる_8回目～EACHを使いこなしてM関数作成～

条件に合うリスト作成

List.FirstN

文法

累計などを集計する時に使用します。条件に合うリストを作成します

=List.FirstN(リスト,条件)

実際の使用例

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

にほんブログ村

M言語

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

2021年3月2日 analytic-vba コメントをどうぞ

【M言語は難しくない！】今回はカスタム列の隠れた裏技を使用して、特殊なテンプレートを作成します。今回は次の画像のように、「1対多」の関係の結合を「コードを1つ追加する」だけでサクッと行います

この処理は、従来であればVBAなどを覚えないとできない処理でした

M言語の仕組みを使えば、カスタム列・作成画面にて本当にサクッと作成できます

M言語の魅力のうちの一つといってもいい処理かもしれませんね！

今回作成するテンプレート

次の画像のように「担当者名のリスト」と「勤務日のリスト」から、各担当者別の勤務表を作成します

上の画像の右側/E列とF列の表のように、担当者1人に対して、複数の勤務日を結合します

勤務表は、最終的には勤務日の右に「勤務開始時刻」や「勤務終了時刻」などを追加して使用するイメージです

　解説は、シート上の「担当者名のリスト/列」と「勤務日のリスト/列」から次の２つのクエリを作成した上で、Power Queryエディタ（以降エディタ）で操作を開始するところからはじめます

・担当者名のリスト ➡ Namesクエリ

・勤務日のリスト ➡ TimeTable

次のファイルに含まれる演習用の元データは、クエリを作成する前の状態になっています

Mコードに慣れる_4回目_演習

1 ファイル 16.96 KB

ダウンロード

今回のポイント

今回の「カスタム列」では、前述の通り「1対多」の関係で結合を行います

次の画像のように「列」ではなく「テーブル」を結合します

この「テーブル」を結合するの点が、今回の解説の最大のポイントになります

今回作成する内容とポイントを確認したところで、本格的な解説をはじめます

続きを読む M言語に慣れる_4回目～特殊テンプレート作成～ →

M言語

指定した範囲の値を使用してフィルタリング

2021年6月6日 analytic-vba コメントをどうぞ

　今回はM関数を使用して、事前にエクセルシートに記入した複数の値を基にしてデータのフィルタリングを行う方法を解説します

M関数を活用する事例となっておりますので、M関数に慣れる機会になれば幸いです

今回使用するM関数は「List.Contains」という関数です

このList.Containsを「each」と組み合わせて活用します

ちなみにM言語では、大文字と小文字を認識するのでContainsのCの入力には注意が必要となります

今回の使用するデータとやりたいこと

今回使用するデータは、次の画像のデータです

＊下の画像では業種IDが4番までしか写っていませんが、実際は12番まであります

上のデータの「業種ID」をいくつかエクセルシート上にフィルタ項目として指定し、指定した業種IDのレコードのみをシートに読み出したいと思います

　上のGIF画像では、事前に「２」「４」をシートに読込んである状態から、フィルタ項目に５を足すことで「２」「４」「５」を読み込みました

今回の処理のポイントは、一度ダミーの値にてフィルタリングしてから、作成されたコードにM関数を組み込むことになります

では、解説を本格的にはじめたいと思います

ダミーの値でフィルタリング

まずは、前述の元データをPower Queryエディタ（以降エディタ）に読込ます

次に、新たにできたクエリを「参照」して更に新たなクエリを作成しておきます

　参照して作成したクエリの「業種ID・列」にて、次の画像のように適当な値をフィルタリングしておきます

すると、数式バーが次の画像のようになっているはずです

フィルタリングを行うためのリスト作成

　エディタ上でフィルタリングを行うためのリストは、エクセルシート上にある前述のフィルタ項目を「ドリルダウン」して作成します

まず、エクセルシート上の業種IDを指定したフィルタ項目をエディタ上に読込みます

次に上の画像の見出しの箇所/フィルタ項目で、右クリックをします

「ドリhttps://analytic-vba.com/power-query/advanced/parameter-drill-down/ルダウン」という項目が出てくるので、こちらをクリックします

すると、次の画像のようにエディタ上にリストが作成されています

＊下の画像では作成したリストの名前を「Filter」にかえてあります

続きを読む指定した範囲の値を使用してフィルタリング →

上級

「例からの列」による桁数が規則列への対応～上級12回目～

2021年1月23日 analytic-vba コメントをどうぞ

こんにちは、Excellent仕事術のガッツ鶴岡（@atsushi1039）です

前回は「例からの列」のメニューを使用して、変換パターンを１つ自作しました

　こちらは１つ例を示して、氏名から「姓」だけ、もしくは「名前」だけを分解する変換パターンでした

下の画像が、変換前の氏名でした

そして「例からの列」機能を使い、上の画像を下の様に分解しました

　では、今回は変換内容の２つの例示を通じ、作成したい変換パターンをPower Queryに汲み取らせた上で、変換パターンを確定する方法を紹介します

この「２つの例示を行うパターン」が使いこなせるようになると、列のデータが不規則でも、ノーコードにてデータ成型が一括で行えるようになります

解説に使うデータは前回と同じです

１．桁数が不規則な列を揃える

今回変換するのは、下の画像の「部門」の列です

こちらは部門コードが4~5桁になっており、桁数が不揃いになっています

この部門コードを3桁で統一します

まずは、例示の1回目を行います

1行目の「ABC1」を「ABC」にて例示すると下の図のようになります

やりたいことは列全体を3桁に揃えることでしたが、3桁になったのは例示した行も含めて2行だけでした

・ABC1 ➡ABC

・ABC10 ➡ABC

他の行は次のような状態です（一部省略）

・BBC2 ➡BBC2

・ABB33➡ABB33

全く変化が起こっていませんので、画面左上の変換式をチェックしてみます

要は「１」の前の文字を切り取る変換式が出来ていました

では、次に下のGIF画像のように「１」を含まない行にて、2つ目の例示を行いましょう

2つ目の例示を追加したら、全て3桁に揃いました

では、変換式を確認してみましょう！

「Text.Start([部門],3)」となっています

こちらは、「部門」の列の各行の文字から3文字を左から切り取ったという意味になります

つまり、２つの例示を通じて「3桁」にしたいという意図をPower Queryが汲み取った形になっています

２．計算

あまり使う機会がないかもしれませんが、「列」と「列」の計算もこの「例からの列」で行えます

「単価」と「個数」を乗じた計算結果を２つ例示してみます

今回は、1行目と2行の計算結果、1800x 7 = 「12600」と3800 x 3 = 「11400」を入力してみます

すると、２つの計算結果を汲み取ってくれて、列の全行に計算結果を入力してくれます

＜まとめ＞

　今回は複数の例示から、変換パターンを汲み取らせて変換するパターンを行いました

　前回の冒頭にも紹介しましたが、複数の列を組み合わせて「文章」も作成することもできます

　但し、大量のデータがある場合には、変換ミスが起こっていても気づかないケースもありうります

なるべく複雑な変換は行わないようにしましょう！

そして、画面左にある「変換式」はなるべくチェックするようにしましょう！

最後に、参考までに今回の解説で使用したデータと完成版を添付します！

次回は、エディタを開かないまま「データソース」を変更する処理を解説します

にほんブログ村

Excellent仕事術

タグ別アーカイブ: Mコード

【Power Query豆知識】ちょっとしたM関数の書き方の工夫でステップ数を削減する方法

カスタム列の追加時

列の削除

＜まとめ＞

UDEMYの動画コース「Power Queryを極めたい人必見！！M関数講座」の無料・勉強会の開催報告

長いクエリの対策

クエリの関数化

＜まとめ＞

M言語に慣れる_3回目～ダイナミックフィルタリング～

目次

今回のポイント

ステップ名変更

参照ステップ

数式バーへの参照ステップ組み入れ

今回使用するデータと行いたいこと

Mコードに慣れる3回目_演習

日付時刻・関数

日付作成/年、月、日

期間値作成

日付リスト作成

リスト系・操作関数

フィルター作業

条件に合うリスト作成

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

今回作成するテンプレート

Mコードに慣れる_4回目_演習

目次

今回のポイント

「例からの列」による桁数が規則列への対応～上級12回目～

１．桁数が不規則な列を揃える

２．計算

＜まとめ＞

誰もが手軽にデータを有効活用してExcellentなビジネスライフを送れるようにします