タグ別アーカイブ: M式

M言語に慣れる_1回目~コード構造の把握~

【M言語は難しくない!】M言語への理解を深めて、Power Queryの「まだ触れたことのない便利機能」を有効活用できるようになりましょう!!

 パワークエリは直感的なクリック操作で一括処理が行えるのが大きなメリットです。ところが、エクセルシート上では簡単に行えていた「セル単位での操作」や、「別シートの参照」、「関数を組み合わせた処理/ネスト」に相当する処理が行えません

要は、Power Queryエディタ内のメニューにある、行列単位の一括処理しか行えないのです

 Power Queryエディタ内のメニューにある処理以外の事をしようとすると、M言語の領域にまで踏み込むしかありません

ところが、詳細エディタや数式バーを見ると、小難しそうなコードが並んでいます

M言語は一見、取っ付き難いのは確かです

但し、特定のルールや使用パターンさえ押さえてしまえば、実はそんなに難しくないはずです

まずは簡単な例にて、M言語を活用することが必ずしも難しくないことを紹介したいと思います

下の画像は、あるクエリのPower Queryエディタ内の画像です

このエディタ内で<赤印の「販売金額」列の1行目の「54000」>の箇所を抽出してみます

エクセルシート上で言えば、「=セル名(例:A9など)」を数式バーに入れる処理です

ちなみに適用したステップには、まだ1つのステップ「ソース」しかありません

この場合、値の抽出は数式バーに簡単な1行を入れるだけで行えます

下のGIF画像の数式バーに注目してください!

=ステップ名[列名]{行位置}という単純なコードを入力しただけです

 但し、コード内の角括弧:[]と波括弧:{}の使い方のルールが分からないと小難しく感じてしまうただそれだけなのです

逆に、[]{}の意味が分かるだけでも「かなりのことが行えそう!」と感じませんか?

少し種明かしをすると、[]がセル名のA列やB列に相当するものです

{}が「A9セル」や「B2セル」の行番号に相当するものです

そしてコード内のステップ名が、セル名の前にを伴って付くシート名(例:平均値の計算!A2)みたいなものです

ルールやパターンが分かれば、エクセルシート上での処理とそれほど違いはないのです

この回では、M言語に慣れるためのウォーミングアップとして、M言語の特徴を3つに絞り解説します

そして、最後にPower Queryの「まだ触れたことのない便利機能」の事例を1点解説します

今回解説する「まだ触れたことのない便利機能」 の便利さを肌で理解した時には、「M言語理解へのハードル」が確実に少し下がっているはずです

1.コードのカラー

まず、Power Queryエディタ(以降、エディタ)で「列のマージ」をしてみます

マージする内容は、下の画像の「姓」と「名」です

「姓」と「名」の間には、スペースを入れます

すると、数式バーが次のような表示になります

上の画像には、黒を除いた「3色」が含まれています

この3色にはそれぞれ意味があります

①赤➡文字列

列をマージする際に、新しく追加される列の「列名」として指定した文字列「氏名」が赤色になっています

②青➡システム予約語

後の回で、個々の用語の意味などは詳細を解説しますが、「each」や「type」などが青色になっています

これらは「システム予約語」と言われるものです

他に「if」「else」なども該当します

システム予約語は、システム言語として、予め使用することが決まっている用語です

③緑➡ハードコード(自動記録)された値

こちらは、エディタ内でステップを追加した際に、ハードコード(自動記録)されたです

上の画像は、列をマージした時、自動的にデータ形式が「文字列」として自動記録されたことを表しています

2.大文字と小文字の区別

M言語を扱う際に、意識しておかなくてはならないことの一つとして「大文字と小文字」の区別があります

仮に、下の画像の「T」を小文字の「t」に置き換えてみます

すると、下のGIF画像のようにエラーになります

M言語を扱う際には、大文字と小文字の区別は特に意識しましょう!

3.ステップの引継ぎ

前述の1.で列のマージを行ったエディタ内には、下の画像のように2つの適用したステップがあるものとして以降の解説を行います

上の画像の2つ目のステップ「挿入された結合列」の数式バーの表示には、「ソース」という名前があるのが確認できます

では、3つ目のステップとして、既存の列を削除したらどうなるでしょうか?

3つ目のステップの中には、2つ目のステップの名前「挿入された結合列」が含まれています

つまり、各適用したステップは、前のステップ名を通じ、基本的には<一つ前ステップの内容>を自動的に引き継いでいきます

4.参照ステップ作成

前述の3.ではエディタ内で<一つ前のステップの内容>を自動的に引き継ぐというPower Queryの特徴について解説しました

但し、これはあくまでも”基本的には”、”自動的には”という話しであり、前ステップをスキップして参照することもできます

では、この「参照のスキップ」を利用した、新たな数字の集計方法を紹介します

エディタ内の話しに戻り、一つステップを追加します

上の画像の「注文金額」の列から全体平均を算出します

すると下の画像のように、「削除された列」ステップを引き継いだ「計算された平均」というステップが追加されます

この「計算された平均」で算出した値「53609」を、他のステップで参照して活用できるようにします

この、他のステップで参照できるようにしたステップを、以降「参照ステップ」と呼ぶことにします

では、この「計算された平均」ステップを「参照ステップ」として確定するための処理を数式バーで行います

下のGIF画像のように「関数のマーク」を左クリックすると、新たなステップが追加されます

「カスタム1」というステップが新たに追加されましたが、このステップはある種、ダミーのステップです

更にこのステップから、下の図のように「前のステップ」をスキップして「削除された列」ステップを参照します

前のステップをスキップして参照するには、下のGIF画像のように数式バーでステップ名「削除された列」を入力し、Enterを押します

すると、エディタの画面が「削除された列ステップ」の内容になります

エディタ画面からは下の画像の「平均金額」は消えましたが、これで「平均金額」を他の画面で参照して活用できるようになりました

今回は、「平均金額」を参照ステップとして活用したカスタム列を作成してみます

作成する内容は「個々の注文金額-平均」です

注文金額については、カスタム列作成の右側から挿入できます

「平均」については、前述のステップ名「計算された平均」を参照します

つまり、「計算された平均」ステップで算出された「53609」が「注文金額」から差し引かれます

カスタム列を作成した後のエディタ画面は、次の画像のようになります

このカスタム列を作成するまでに、ステップを新たに追加したり、ステップをスキップしたりしたので、図で整理して今回の処理を振り返ります

ⅰ)カスタム1の列をダミーとして追加

この処理で追加されたステップにより、「計算された平均」ステップを残したまま、エディタ画面を「削除された列」ステップの画面に戻せています

つまり、「削除された列」➡「計算された平均」というステップの順序が、「計算された平均」➡「削除された列」という順序に変えることができました

ⅱ)カスタム列の作成

ⅰ)で変えた順序を活かして、個々の注文金額マイナス全体の平均を計算します

<まとめ>

今回は「M言語に慣れる」の1回目として、M言語の特徴を3つに絞って解説しました

1.コードのカラー

2.大文字と小文字の区別

3.ステップの引継ぎ

 上の3つを意識してPower Queryを活用してもらえると、M言語が必ずしも難解なわけではないことが、分かって頂けると思います

そして、3つの特徴の後には3.ステップの引継ぎを応用した「参照ステップ」について解説しました

こちらでは、ステップの順序を入れ替えて「ステップで算出した値」を有効活用できるようにしました

ⅰ)一旦、注文金額の平均値を計算➡参照ステップ

ⅱ)カスタム列・作成画面で「各行の注文金額-平均値/参照ステップ」を計算

この上のⅰ)ⅱ)の処理はエクセルシート上で例えるなら、以下の画像のような処理です

 データが存在するシート/画像左とは別のシート/画像右で平均値を計算しておき、元のデータが存在するシートで注文金額から平均値を引いています

ですので、今回使用した参照ステップの値はエクセルシートでの操作でいえば別シートでの計算です 

 M言語を使用していけば上の事例と同じ様に、エクセルシート上での細かい操作に近いことが一括で行えることが理解できてきたと思います

 では、次回からはM言語を有効活用してPower Queryの魅力を新たに引き出す方法について、もっと具体的に解説していきます

M言語の記事一覧については、ここから見ることができます

記事を最後までお読み下さり、誠にありがとうございました

参考までに解説に使用したエクセルファイルを添付します

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


にほんブログ村

M言語に慣れる_5回目~M関数を検索する方法~

【M言語は難しくない!】今回は使用方法に合ったM関数を、Power Queryエディタ(以降エディタ)上で検索する方法を紹介します

エクセル関数では、エクセルシート上で直接、関数を検索できます

実は、Power Queryでも同じような機能があります

エクセル関数と同じ様に検索した後に、そのまま使い回すことはできませんが、この機能を覚えておくととても便利です

目次

準備作業 / 空のクエリ作成

M関数一覧を取得

一覧をテーブル化

実際に検索してみる

<まとめ>

準備作業 / 空のクエリ作成

まずは準備として、空のクエリを作成します

「データタブ」➡「データの取得」の順でクリックします

次に「その他のデータソース」を選択し、「空のクエリ」をクリックします

M関数一覧を取得

空のクエリの数式バー上で=#sharedを入力し、エンターを押してください

すると、M関数の一覧が表示されます

一覧をテーブル化

表示された一覧を検索できるようにします

画面上の「テーブルへの変換」をクリックします

すると一覧がテーブルに変化され、フィルター機能が活用できるようになります

実際に検索してみる

①Text関数

前述の検索画面では、残念ながら日本語では検索できません

英語である程度アタリをつけて検索を行う必要があります

今回は、「Text」を文字列に含むM関数を、下の画像のように「行のフィルター」で検索してみましょう

検索文字列の指定時は、大文字と小文字の区別には注意しましょう

過去記事でも触れましたが、M言語では、大文字と小文字は厳密に区別されます

上の画像が「Text」文字列で検索を行った時の状態です

②Text.PositionOf関数

この「Text」文字列を含むM関数の一覧から、上から33番目にある「Text.PositionOf」を実際に参照してみます

上の画像の赤丸にあるように、「Text.PositionOf」の右横にある「Function」のテキストがありますので、そちらをクリックします

すると、下の画像のように「Text.PositionOf」関数の機能を事前に検証できるダイアログが開きます

上の画像の黄色で印をつけた箇所を読むと、文字列の中から「検索文字の開始位置」を返すM関数であることが分かります

試しに文字列を「TEST」、検索文字を「E」と指定して、画面右下のOKボタンをクリックしてみます

すると、上のGIF画像のように「1」と出力されます

文字列「TEST」の中から「E」を検索しているので、読者の方の中には「2」が正しいのでは?、と思われた方もいらっしゃると思います

これは、M言語が「0」をベースにしているためです

M言語では、1ではなく0からカウントがはじまります

ですから、文字列「TEST」の中から「T」を検索すると、「1」ではなく「0」が出力されます

なお、

上の画像のように「0」が表示されている段階では、適用したステップは次の画像のようになっています

参照したM関数の詳細を確認するには、最終ステップから一つ前の「Value」に戻します

ステップを一つ戻すと、上の画像のように詳細な解説が表示されています

更に下にスクロールすると、M関数の使用例も確認できます

③カスタム列作成画面で活用

「Text.PositionOf」関数を、他のクエリで実際に使用してみましょう

先ほど、紹介した「Text.PositionOf」の使用例をコピーしておきます

次に、エディタ画面左から他のクエリを開きます

今回の解説でM関数を使用してみるのは、下の画像の「部門」の列です

この「部門」の列から文字列「B」の位置を検索した結果を、新たな列の「結果」に出力してみます

では、「列の追加」タブから「カスタム列」をクリックします

次に、開いた「カスタム列・作成画面」に先ほどのコピーした使用例を貼り付けます

そして、上の画像にて黄色の印/コンマをつけた箇所の左には、画面右の「使用できる列」から「部門」を挿入し、既存の”Hello,・・・”と置き換えます

そして、黄色の印/コンマの右は、Bで置き換えます

コンマの左右を置き換えた後は、右下のOKボタンをおします

すると、次のような結果が出力されます

上から2番目だけは、Bから部門名がはじまっているので「0」と出力されています

<まとめ>

今回は、エディタ上でM関数を参照する方法について解説しました

 空のクエリを作成し、数式バーに「#shared」と入力すれば、M関数の一覧が出力されます

更に、一覧をテーブルに変換すれば「検索」も行えるようになります

エクセル関数の参照機能と違い、日本語で検索できないのが、不便ではあります

 後、エクセル関数の参照機能では参照した関数を実際に使い回すことができますが、M関数ではその機能はありません

 但し、表示された使用例をコピーして変更していくだけでも、だいぶM関数を有効活用できるようになります

ぜひ、実際に手を動かして参照機能からM関数を使用してみてください

これまでよりも、Power Queryをより身近に感じられるようになるはずです!

尚、過去の記事でも解説させて頂きましたが、M関数についてはMicrosoft社のページでも各関数を参照できます

Microsoft社のM関数の解説ページには、エディタ内のカスタム列・作成画面から直接遷移できます

カスタム列・作成画面

今回の解説は以上です

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

次回は、M言語で難解なことの1つである[]と{}の組み合わせについて解説します

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

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

にほんブログ村

M言語に慣れる_6回目~3種類の括弧~

【M言語は難しくない】Power Queryを扱っていると、3種類の括弧が出てくるのが分かります。 「M言語が難解」と感じる理由の一つは、この3種類の括弧が組み合わせられて出てくるからです。

・丸括弧 / ()

・角括弧 / []

・波括弧 / {}

特に下の2つの括弧の[]{}は、初めて見たときは意味合いが全く不明です

実は、この2つの括弧M言語を使いこなすための重要な鍵です

M言語を学ぶ目的の一つは、行列単位での一括処理からの脱却です

シート上でセルを扱うように、Power Queryエディタ上でも1つの値を扱いたいのです

少し、シート上でのセル操作についてお話したいと思います

セル名は例えば「A8」であれば、Aは列名で8は行番号です

この列名と行番号を組み合わせれば、望みのセルにたどり着きます

実は角括弧:[]はシート上の列名に、そして波括弧:{}行番号に相当します

今回はこれらの括弧に、次の2つの方法を通じて慣れて頂くとともに、それぞれの意味合いを掴んて頂きたいと思います!

なお、解説は今回を1回目として、2回に分けて行います

・既存のクエリから角括弧 / []と波括弧 / {}を使い、1つの値を参照する ➡1回目

・空のクエリから3種類の括弧を使って、テーブルを作成する ➡2回目

この3つの括弧の意味合いを掴んだ時には、M言語への苦手意識が薄れているはずです!

目次

今回のポイント

括弧を使ってデータ参照

<まとめ>

今回のポイント

今回の解説にあたっては、そもそも、Power Queryとは何なのか?という点がとても重要です

エクセルはもともと表計算ソフトであり、データベースではありません

Power Queryは、エクセルをデータベースとして使用できるようにするツールです

ですので、エディタ内ではデータベースとして使用するための「データの構造化」が行われています

この構造化が「Power Queryは何か?」を理解するためのキーワードです

まずは、構造化には4種類の概念があることを常に頭に入れておきましょう!

①値

値の種類には、主に次のようなものがあります

・1/ 整数(数値)

・b / テキスト(文字列)

・2021/03/04(日付)

今回の解説で使用する値の概念は、もっと本源的なものです

例えば、1は数字ですが、データベース上は2つの型式になりうります

今回解説する値の概念に照らすと、1は整数でもない文字列でもない、ただの「1」です

②リスト

①の値が、列方向に順次並べられたものです

通常の列とは違います

その点は後で解説します

③レコード

行方向に、複数の②のリストを1行で組み合わせたものです

④テーブル

上の②と③を組み合わせたものです

これらの4つの概念を頭に入れたところで、本格的な解説をはじめます

括弧を使ってデータ参照

今回は、3つの列から構成される、次の画像のデータを解説に使用します

上の画像の表から括弧を使って、様々な参照作業を行うわけですが、まずは参照作業を行い易くします

ちなみに、この章では角括弧 / []波括弧 / {}による参照しか行いませんので予めご了承ください

 参照作業を行い易くする為に、下のGIF画像のように数式バーのマークをクリックし、数式バーの内容を前ステップ名で確定させてます

これで数式バーにて、前ステップ名の次に括弧をつなげていくだけで参照作業ができるようになります

角括弧 / []による参照

角括弧 / []の中には、基本的には列名のみが入ります(列名以外も入れるパターンは後述)

では、数式バーに次の内容を入力してみます

➡ = 変更された型[連番 / 1列目]

すると、次のGIF画像 のように、前述のデータの1列目が参照されます

下の画像の赤丸の箇所に注目してくみてください

リストとして出力されているのが良く分かります

 元の「列名」である「連番」の記載がどこにもなく、データ形式「整数」の表示もありません

変換タブでリスト変換した時と同じ画面になっています

前ステップの列の内容をそのまま参照する場合には、角括弧 / []を2重にして[[連番]]にします

波括弧 / {}による参照

波括弧 / {}の中には数字が入ります

波括弧 / {} に入れる最小の数字は0です

0から始まるのは、M言語が過去記事で紹介したように0をベースとしているからです

では、角括弧 / []と同じデータを使用して、波括弧 / {}の中に数字を入れてみましょう!

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

まずは波括弧 / {}に 0をいれてみます

➡ = 変更された型{0}

すると、1行目の「レコード」が参照されます

ちなみに上の画像は、レコードツールという表示になっています

次に、波括弧 / {}に 1をいれてみます

➡ = 変更された型{1}

こんどは2行目のレコードが参照されます

角括弧と波括弧の組み合わせによる参照

角括弧と波括弧の組み合わせでは、2種類の参照方法を紹介します

1つ目は、角括弧と波括弧を並列で組み合わせます

角括弧と波括弧の組み合わせにより、「1番目のレコード」の「連番列」の値を抽出します

= 変更された型{0} [連番]

こちらは画面上をみると、リストでもレコードでもなく、値が参照されているのがわかります

こちらは、ドリルダウンを行った時と同じ内容です

2つ目は、波括弧 / {}の中に角括弧 / []を入れます

角括弧 / []の中には、次の画像のように「列名=列の中味」を入れ、列の中味を通じてレコードを参照します

= 変更された型{[連番=1]}

すると、1行目のレコードが抽出されます

<まとめ>

 今回は、角括弧 / []と波括弧 / {}を使用して、エディタ内で参照作業を4つ行いました

1.角括弧 ➡リストを参照

2.波括弧➡レコードを参照

3.角括弧と波括弧の組み合わせ①➡値を参照

4.波括弧の中に角括弧を入力➡レコードを参照

 記事の冒頭で、Power Queryの「データ構造化」には4つの概念(値、リスト、レコード、テーブル)があると紹介しました

実際に手を動かして、上記の4つの作業を行ってみて、4つの概念について肌で感じて頂けると幸いです

 4つの概念を肌で感じた時には、Power Queryへの理解が一段と深まっているはずです

今回は、以上です

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

次回は、空のクエリから3種類の括弧を使用してリスト、レコード、そしてテーブルまで作成します

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

にほんブログ村

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

【M言語はむずかしくない!】 数式バーでよく見かけるコードの一つに、青字の「each」というコードがあります。青字ということは、システム予約語なので、数式バーに頻繁に登場します。ですので、「each」に慣れれば、自ずとM言語自体にも慣れ親しんでいけます。今回はこの「each」についての事例数多く紹介します。

多くの事例を通じて「each」自体についての意味合いを掴み、M関数内でのeachの使い方に慣れていきましょう!

目次

今回のポイント

「each」の使用例

「each」の中味について

M関数を作成する / List.Select

<まとめ>

今回のポイント

「each」とセットでよく出てくるコードに「アンダースコア / _」があります

「each」と 「アンダースコア / _」の2つは切っても切れない関係にあります

何故なら、「each」は「アンダースコア / _」を使用した関数を簡略したものだからです

ですので、今回は 「アンダースコア / _」についても解説します

 但し、「each」と「アンダースコア / _」の詳細な内容は難解なので、「こういうもの」だと割り切って読み進めてください

そして、最後にM関数「List.Select」を「each」と「アンダースコア / _」を使い作成します

実際に手を動かしてみて「each」と「アンダースコア / _」に対する感覚を掴んでみてください

M関数の作成にあたっては、前回も行ったようにM関数の使用例を活用します!

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

「each」の使用例

まずは、「each」の理解の入り口として「each」の使用例を2つ紹介します

フィルタリング

下の画像のように、エディタ内に「氏名」「部門」という列名の列があるデータがあるとします

こちらの部門列から「営業」をフィルタリングします

すると、数式バーは以下の表示になり、「each」が条件式に合うものに対して使われているのが分かります

抽出

前述のデータにて、今度は「氏名」の列から「苗字」を抽出します

使用するメニューは「変換」タブ内の「抽出 / 区切り記号の前のテキスト 」になります

区切り文字として使用する記号は「半角スペース」を使用します

すると、数式バーは以下の画像のような表示になり、「each」と「アンダースコア」(“BeforeDelimiter(“の後に表示)が使用されています

上の画像だと分かりずらいと思いますので、文字列に直して表示し直したものも確認しておいてください

= Table.TransformColumns(フィルターされた行, {{“氏名”, each

Text.BeforeDelimiter(_, ” “), type text}})

「each」の中味について

Microsoft社の「each」についての解説を見ると、「簡略化された宣言」と解説されています

「each」について、シンプルに解説しようとしたら、エディタ内ではなくエクセルシート内のテーブルにて解説した方がいいかもしれません

上のGIF画像のように「=」を使用して「部門」列を参照すると、一気に「部門」列内の値が抽出されます

これが「each」の役割です

もっと技術的な言い方をすると、記事の冒頭で前述したように「アンダースコア / _」を使用した関数を簡略したものです

この点については、実際の使用例で解説します

「each」の使用例で紹介した下の画像の数式を、「アンダースコア」で書き直してみます

画像に alt 属性が指定されていません。ファイル名: 営業-644x57.jpg

一旦、仮の変数のmyRuleで「each」を置き換えます

更に、上の画像のmyRuleを「アンダースコア」で置き換えます

これで、「each」が「アンダースコア」で置き換えられました

この「アンダースコア」で置き換えられた関数の簡略化したものが「each」ということです

但し、この解説では何のことかよく分からないと思います

そもそも「アンダースコア」が何なのか?ということすら不明です

「アンダースコア」が何かを知るのに一番いいのが、カスタム列を「アンダースコア」で作成することです

カスタム列を「アンダースコア」で作成すると、エディタ内は以下の画像のような列が作成されます

作成された数式は以下の様に、「each」と「アンダースコア」が組み合わせられています

上の画像の「Record」をクリックすると、それぞれのレコードが開きます

この画像を見るに、「アンダースコア」とは各レコードの内容を仮受したものとして理解するのがいいと思います

M関数を作成する / List.Select

では、「each」を使用してM関数を作成してみましょう!

「each」と「アンダースコア」 を使って、M関数「List.Select」を作成し、前述のデータから「部門」が「営業」の部門を抽出しましょう!

前述の解説に使用したデータを元の状態に戻した後に、部門の列をリスト化します

すると、数式バーには以下の様になっています

次に上の画像の数式マークをクリックして、一旦、確定処理を行います

これで、数式バー内が前ステップ名「部門」を参照するだけの内容になりました

次に、一旦は「List.Select」と入力し、エンターキーを押します

前ステップ名は消えてしまって構いません

すると、上の画像のようにM関数「List.Select」解説が表示されます

そして、解説画面をスクロールしていくと使用例が表示されています

上記画像の数式部分を抜き出すと次の通りです

➡ List.Select({1, -3, 4, 9, -2}, each _ > 0)

この内容を見ると、リストの中の内容(1,-3,4,9,-2)を、

ⅰ)「アンダースコア」が仮受

ⅱ)ⅰのアンダースコアが、仮受したものの中で条件に合うものを抽出

ⅲ)eachでⅱで抽出されたものの「それぞれ」を指定

という内容になっています

では、この使用例を活用して実際にM関数を入力します

➡ List.Select(部門/前ステップ名 , each _ = “営業”)

これで「each」と「アンダースコア」 を使ってM関数が作成できました

<まとめ>

今回は「each」と「アンダースコア / _」について解説を行いました

解説の中では細かい説明も行いましたが、

アンダースコア / _」がレコードの内容を仮受けするもの

・「each」が、「アンダースコア」が仮受した内容から、ある種の条件にて抽出された内容の全体を示すもの

との理解でもいいと思います

「each」と「アンダースコア / _」 の定義を突き詰めると難解ではありますが、使用例を数種類見ていくと、感覚的にはそんなに難しくはないと思います

 解説の中で示した使用例を活用し、実際に手を動かしてM関数を作成していくと「each」と「アンダースコア / _」に早く慣れることができると思います

今回の解説は以上です

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

次回は、M関数を使用して複数シートを一気にまとめる方法について解説します

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

にほんブログ村

【分析】Power Queryで手軽にABC分析

 

 ABC分析は「重点分析」とも言われ、分析手法の中でも最も基本的なものです。ところが、実際にエクセルでABC分析用の表作成を行おうとすると「データのバケツリレー」の手間を要します。データのバケツリレーは「列から列」へと続き、数式を細かく設定する手間もかかります。今回紹介する方法であれば、Power Queryエディタ画面(以降、エディタ)でシンプルに処理していけます!

以下が、通常のABC分析の表を作成するときの手順です

①重点管理する項目(金額など)を降順で並び替え

②①を累計で表示 ➡「=」から始まる数式を入力

③②を比率に変換➡ 事前に累計の値を算出 ➡累計の値で各行を割り算

④③をランク付け➡IF式を入力

もし、データの追加などがあったら、①~④の修正作業が必要です

もちろん、データ自体が変われば、①~④の更新処理が必要です

Power Queryであれば、一度、クエリを作成してしまえばデータ更新時に「ソース変更」「クエリの更新」をクリック処理をするだけで済みます

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

使用データ

今回は、下の画像にある2つの列から構成されるデータを使用します

行いたいこと

前述の使用データの各行に、次の内容でランクを付けます

A ➡全体・販売個数の50%以下

B ➡ 全体・販売個数の80%以下

C ➡ 全体・販売個数の80%超

今回のポイント

今回使用する主な技術は、主に以下の1~3の内容です

1と2は過去に紹介した内容です

最後の3.List関数が今回の一番のポイントになります

1.並び替え/降順

まずは元データを降順で並び替えます

2.条件列の追加

「列の追加タブ」の「条件列」メニューでランク付けを行います

3.List関数

このList関数で累計値と比率を計算します

List関数は2種類使用します

まず1つ目は、List.Select関数です

リストから条件に沿う値を抽出し、リストを再作成します

書き方は次の通りです

List.Select(リスト,条件)

上の画像では、1~4の値からなるリストから、2超の値を抽出した上でリストを再作成しています

2つ目は、List.Sum関数です

こちらの関数は、直感的にも分かり易いと思います

List.Sum()の丸括弧の中に入れたリスト内の各値を合計します

但し、丸括弧の中に入るのはあくまでリストであり、列ではないことに注意しましょう!

では、今回のポイントを紹介したところで本格的な解説を始めます

尚、解説はエディタ内のみの内容になります

並び替え

まずは、エディタ内で販売個数を降順で並び替えます

累計値

算出ロジック

List関数を組み合わせて累計値を計算するのが、今回の記事の最大ポイントです

累計値を計算するロジックについては、次の画像を基にして解説します

通常のABC分析では、N行目のとN-1行目の累計値の合計を計算します

今回の記事では、上の画像のようにⅰ)N行目の値以上の値のリストを作成、ⅱ)ⅰのリストを合計、というⅰ)⇒ⅱ)の流れで行います

List.Select関数

まず、カスタム列・作成画面で「販売個数」列を挿入してA列を作成してみます

この処理だと下の画像のように、販売個数と同じ列ができるだけです

では、カスタム列・作成画面で前ステップ名(カスタム列を作成するステップの1つ前)を入れてみましょう

この段階では、前述の「降順に並び替えられたステップ」が前ステップです

下の画像が、前ステップ名を入れたカスタム列・作成画面の画像です

上の画像の内容でカスタム列を作成すると、次の画像のように各行にてリストが作成されます

このリストの中味は、一律に販売個数の列の内容です

この各行のリストから、前述のロジックの通り「各行の値以上」のリストを各行に再作成していきます

こちらのリストの再作成は、List.Select関数で行います

条件式は、一部、過去の記事で解説したカスタム関数の内容を使います

上の画像のように、「(x)=>」にてxを変数として宣言し、「x>=」を条件式とします

この内容でカスタム列を作成すると、行毎に作成されるリストの内容が変わっています

例えば、上の画像のように2番目の行であれば。2つの値しかリストの中にありません

では、前述の紹介したロジックの通りにリストが再作成できたので、List.Sumの解説に移ります

List.Sum関数

では、List.Select関数で作成したリストをList.Sum関数で合計し、累計を作成します

上の画像のようにカスタム列・作成画面に数式をセットしてOKボタンを押すと、累計の列がエディタ内に追加されます

比率

では、累計を計算したので「比率 =各行の累計値÷販売個数の合計値」 を計算します

販売個数の合計値の算出には、前述のList.Sum関数を使います

ちなみに、上の画像の「追加されたカスタム」とは前ステップ名です

ランク付け / 条件列

では、最後にランク付けを行います

ランク付けは「列のの追加タブ」にある「条件列」で行います

記事の冒頭にあったように、ランク付けの条件を「条件列の追加」画面に設定します

比率が0.5以下であれば ➡ A

比率が0.8以下であれば ➡ B

とします

上記の条件以外はCとして出力するようにします

この条件列を作成すれば、ABC用の表作成は完成です

<まとめ>

今回は、Power QueryでABC分析の表を作成しました

一番のポイントは、List関数を2つ組み合わせて「累計」の列を作成する点です

List関数の中には、常に「リスト」を指定します

M言語では、「リスト」と「」は明確に違います

今回の内容では、List関数の丸括弧の中は「ステップ名[列名]」で指定しました

この「ステップ名」を指定する場合があることを強く意識しておけば、後はそれ程難しい点はありません

 私は以前、企画の仕事をしている時にABC分析表を毎月作成しておりましたが、面倒で仕方ありませんでした

 今回、紹介したPower Queryの仕組みであれば、一度クエリを作成してしまえば、毎月ほぼ「ソース変更」「クエリ更新」をクリック処理で行うだけで済みます

分析で一番大事なのは、数字を解釈することです

 今回紹介した仕組みで、表作成を効率化して有意義な分析を行えるようにしていきましょう!

次回からはRFM分析の解説をはじめます!

https://analytic-vba.com/power-query/analysis/recency-group/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

M言語に慣れる_9回目~複数シートをコード1行で結合~

【M言語は難しくない】今回は、空のクエリM関数を1つ入力するだけで複数シートを一気に結合できるようにします!今回紹介するM関数を使いこなせば、面倒なシート間のデータのバケツリレーから抜け出すことができます

 Power Queryで複数シートを結合する方法として一般的に紹介されている方法では、次の画像の処理「クエリの追加」が必ず入ると思います

今回は、「クエリの追加」は使わず空クエリの数式バーに「=Excel.CurrentWorkbook()」を入力します

1つM関数を入力するだけで、ファイルに含まれるシート内のテーブルが全てエディタ内に反映されます

これで、シート間のバケツリレーの作業からは抜け出すことができます!

但し注意点もありますので、そちらもあわせてM関数/Excel.CurrenWorkBookの使用方法を解説します

目次

今回のポイント

今回使用するデータ

準備作業/空のクエリを作成

M関数の入力/Excel.CurrentWorkbook

読み込み処理

データの循環対策

シート追加テスト

<まとめ>

今回のポイント

今回はM関数を入力した後に、1点だけ注意点があります

それは、循環問題です

複数シートを1つにまとめた内容/クエリが、M関数に反映されてしまいます

ですので、クエリを更新すると読み込み行数が倍になります

対策として、適用したステップに1つのステップを追加します

今回使用するデータ

今回使用するデータには、シートが3つ含まれています

1シートが1か月分の出荷データになっています

各シートの内容は、事前に「シート名/Data_y年m月」をテーブル名にしてテーブル化してあります

次のファイルが今回、実際に使用するサンプルデータです

準備作業/空のクエリを作成

まずは空のクエリから作成します

「データタブ」➡「データの取得」の順でクリックします

その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします

そして、下の画像にある「空のクエリ」をクリックします

するとエディタが開き、空のクエリが立ち上がります

M関数の入力/Excel.CurrentWorkbook

記事の冒頭でも解説しように、 「=Excel.CurrentWorkbook()」を入力します

上の画像のように3つテーブルが表示されたら、2つ処理をします

まず、上の画像の「Name」の列を削除します

次に下の画像の黄色に印をつけた箇所から、各テーブルの内容を展開します

展開する際には、次に開いた画面にて、「元の列名をプレフィックスとして使用します」のチェックを外しておきます

上の画像のOKボタンをクリックすると次の画像のように、各テーブルが展開します

出荷日については、データ形式を日時型式ではなく、日付形式にしておきます

読み込み処理

読み込み処理は、新規のテーブルに行います

上の画像の「閉じて次に読み込む」をクリックし、次に開いた「データのインポート」画面にて、新規のシートを指定します

新しく追加されたシートは、シート名を「小計」に変更しておきましょう!

データの循環対策

まず、新しく作成したクエリをエディタで開き直します

エディタを開いたら、適用してステップの「ソース」を選択します

すると、下の画像のように4つのテーブルが表示されています

循環対策として、テーブル名に「Data」を含むものだけが展開されるようにフィルターをかけるステップを追加します

フィルターをかける際には、下の画像のように「指定の値で始まる」を指定し、「指定の値」として「Data」を指定します

すると、次の画像のように「Data」から始まるテーブル名のテーブルだけが表示されます

ここまで、行ったらエディタを閉じて読み込みます

シート追加テスト

適切に循環対策が機能しているかどうかを調べるため、「Data_2020年4月」のテーブル、及びシートを追加します

追加するシートには1行だけデータがありますので、循環対策が適切であれば、更新処理時に1行だけが増えるはずです

では、更新を行ってみます

上のGIF画像のように、適切に1行増えています

<まとめ>

今回はM関数「=Excel.CurrentWorkbook()」を 空のクエリを入力して、各シートのテーブルを一括でまとめました

 後からテーブル/シートを追加したとしても「データの結合」処理を行わずに済むので大変楽な方法かと思います

但し、1点だけ注意点があり、それが循環対策です

 複数のテーブルをまとめた内容自体が、「まとめ」の内容に含まれないようにフィルター処理を追加しました

Power Queryにおけるエラー対策として、フィルター処理はとても有効です

この点は今回を機に、しっかりおさえておきましょう!

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

今回使用したファイルの完成版を添付しておきます

次回はグループ毎に連番を付与する方法を解説します

https://analytic-vba.com/power-query/m-code/begin-group-index/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

M言語に慣れる_10回目~グループ毎に連番作成~

【M言語は難しくない】今回はデータをグループ化した際に、1から始まる連番をグループ毎に作成する方法について解説します。この処理の仕方を覚えると、エクセルの使い方の幅が広がります

コードの採番や顧客の2回目のリピート状況把握など、使いみちは沢山あります!

しかも知られざるグループ化機能を使えば、1つのM関数を入力するだけでできてしまいます

まさに魔法です

ぜひ、実際に手を動かしてこの魔法を体験してください!

目次

今回のポイント

今回の使用データ

グループ化の実施

M関数の入力

<まとめ>

今回のポイント

今回のポイントは2つあります

グループ化

グループ化する際に、よく選択される操作は「合計」などです

今回は「すべての行」という操作を選択します

 この「すべての行」を選択して操作を行うと、グループ毎テーブルが作成されます

M関数作成

連番は「列の追加」タブの「インデックス列」のメニューから、クリック操作で簡単に作成できます

 今回は、既存のメニューは使用せず、カスタム列の作成画面から「Table.AddIndexColumn」というM関数を使用して「連番」を作成します

 M関数で作成することで、ポイントの1点目で作成されたグループ毎のテーブルを、関数の引数として指定できるようになります

今回の使用データ

今回は下の画像にあるデータの「部門」列をグループ化します

グループ化した後は、グループ毎に連番を振ります

以下が実際に使用するサンプルデータです

グループ化の実施

解説は、前述のデータをテーブル化し、エディタを開いたところから行います

エディタを開いたらまずは、グループ化を行います

グループ化は「ホームタブ」の「グループ化」メニューから行います

次に開いた画面では、前述のように操作を「全ての行」で設定します

・グループ化対象列 ➡ 「部門」列

・新しい列名 ➡ 「部門列」

・操作 ➡ 「すべての行」

空欄のままでOKです

上の内容で指定してOKボタンをクリックすると、エディタ内では次の画像のように、テーブルがグループ別に作成されています

M関数の入力

関数の参照

次に「列の追加」タブから「インデックス列」を挿入してみます

すると、確かに連番は作成されますが、グループ毎の連番ではありません

こちらのステップについては後で削除するとして、まずは過去の記事のように関数の解説を参照してみましょう

関数の後の丸括弧を全て削除した後にエンターキーを押し、解説を表示します

テーブルに新しい名前の列を追加するという内容から解説が始まっていますが、関数の作成の仕方については、次のような内容が書いてあります

=Table.AddIndexColumn(テーブル名,”新列名”,連番の開始番号,連番の増分)

このM関数では、引数を4つ指定します

・テーブル名

・新列名

・連番の開始番号:通常は1から指定します

・連番の増分:通常は1つずつ連番を増やします

この数式の内容に従い、カスタム列作成画面から「連番」を作成し直します

なお、既に追加した連番については削除しておきましょう

カスタム列作成

それでは、「列の追加」タブから「カスタム列」をクリックします

次に開いた画面では、M関数にてカスタム列を作成していきます

使用するM関数は、前述の次の関数です

=Table.AddIndexColumn(テーブル名,新列名,連番の開始番号,連番の増分)

こちらは、入力補完機能を活用して入力していきます

まずは、「INDEX」と入力してみましょう

すると上のGIF画像のように「Table.AddIndexColumn」が選択できるようになります

↓で2つ下にカーソルを移動し、TABキーで該当のM関数を選択します

次に、前述のようにテーブルを引数の1つ目に指定します

こちらは、列の挿入で代用します

画像に alt 属性が指定されていません。ファイル名: 3つのテーブル.gif

上のGIF画像のように「部門別」の列は3つのテーブルから構成されています

ですので、下の画像の「部門別」を挿入すれば、自動的に3つのテーブルを引数として設定できます

下のGIF画像のように「部門別」列を挿入したら、残りの3つの引数もそれぞれ指定します

残りの3つの引数は、次の内容となります

・新列名 ➡ 連番

・連番の開始番号 ➡ 1

・連番の増分 ➡ 1

下の画像のように、上で紹介した3つの引数を設定したら、OKボタンを押します

すると、新しく追加した「連番」の列名の列に更に3つテーブルが作成されます

この後は、下の画像の赤い丸印をつけた箇所から展開する処理になります

その前に、他の列は削除しておきます

他の列を削除したら展開処理します

上のGIF画像のように、次に開いた画面で「元の列名をプレフィックスとして使用します」のチェックは外しておきましょう!

すると上の画像のように展開されます

きちんとグループ毎に連番も作成されています

グループ毎に適切に連番が作成されていることを確認したら、読み込み処理を行います

<まとめ>

今回はグループ毎に連番を作成する方法について解説しました

作成にあたってはポイントが2点ありました

1つ目は、グループ化の際に「操作」の指定を「すべての行」にする点です

画像に alt 属性が指定されていません。ファイル名: グループ化全ての行.jpg

こちらの処理により、グループ毎にテーブルが新規に作成されます

 2つ目は、既存のメニューにより「連番」を作成するのではなく、カスタム列・作成画面からM関数にて「連番」を作成する点です

 カスタム列からM関数を作成することにより、1つ目のポイントで作成した「グループ毎のテーブル」を引数にして関数を設定できます

 今回の方法はエディタ内の1つの列に、複数のテーブルが作成される点が少しトリッキーです

 但し、今回の処理はデータ分析やデータ整理などで使える場面が多いはずです

 グループ毎に連番を作成した後、クエリを「複製」していけば、連番別に更に階層化してデータ整理が行えたりできます(例:連番1のレコードのみを集める等)

 今回解説した方法の活用事例等についても、今後はこのブログで発信していきたいと思います!

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

参考までに今回使用したファイルを添付します

次回は、前行を参照する方法を解説します

https://analytic-vba.com/power-query/m-code/begin-previous-ref/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

M言語に慣れる_11回目_前行を参照して計算

【M言語は難しくない】今回は連番波括弧:{}を組み合わせて活用して、前行を参照する仕組みを作成します。前行を参照する仕組みを作成したら、下の図のように「前行との差額」を計算します。

この処理はエクセルシート上であれば、四則演算の式を挿入すれば簡単に行えます

=(列名/N行目)-(列名/N-1行目)

但しセル単位/1つの値単位の操作になるので、M言語を使用しなければPower Queryでは行えません

M言語を使用すればできるといっても、とても難しい処理なのでは?

いえ、決してそんなことはありません!

 エクセルシート上の行番号/レコードに相当する波括弧:{}を、連番/0,1,2・・・とうまく組み合わせて使いこなせば、簡単にできます

 実際に手を動かして前行を参照した計算が行えるようになった時には、M言語の活用に大き自信を持てるようになっているはずです!

https://www.youtube.com/watch?v=VmnYfZgUeCA

目次

今回のポイント

今回使用するデータ

連番の追加

レコードの参照

カスタム列・作成画面で連番を操作

<まとめ>

今回のポイント

過去の記事で、丸括弧と波括弧を組み合わせてエディタ内のデータを参照する方法について解説しました

 括弧を使用して参照する方法は、下のGIF画像のように<前ステップ名 + 波括弧/レコード位置 + 角括弧/リスト>を組み合わせる方法でした

今回は、この「括弧の組み合わせて参照する仕組み」を、更に「連番 /0,1,2,3・・・」と組み合わせて活用します!

今回使用するデータ

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

上の画像のB列にある「株価」を使用して、計算を行います

各行にて、前行との株価の差額を計算します

連番の追加

今回の解説は、前述の使用データをエディタにて開くところから始めます

まず、「列の追加」タブから「インデックス列」のメニューをクリックし「連番」を追加します

インデックス列は「0」から始まるように指定します

1ではなく0にて開始する理由は、M言語は「0ベース」だからです

この追加した「インデックス列/連番」を「レコードの参照」に応用します

レコードの参照

前述の「インデックス列/連番」を「レコードの参照」に応用する点について、具体例で解説します

上の画像のように、エディタ内にインデックス列/連番を追加した段階では、適用したステップは下の画像のようになっています

まず、画像内の最終ステップの上で「F2キー」を押して、ステップ名を「DATA」に変更します

その後に、数式バーのマークをクリックして、「DATA」ステップを確定させます

これで、「DATA」ステップを前ステップとして参照する「確定処理」ができました

この確定処理を行った段階にて、適用したステップは次の画像のようになっています

ここから、インデックス列/連番を活用して、手動で各株価を参照してみましょう!

まずは、下の画像にて黄色の印をつけた箇所の株価を参照してみましょう

こちらは、インデックス列の番号「0」を、下の画像のように波括弧 / {}内に使用します

*角括弧:[]と波括弧:{}を使う順番は、前述の「今回のポイント」で解説した

次は、4行目を参照してみましょう

こちらは、インデックス列の「3」を使用します

実際に例を使い、インデックス列の値を使用して株価を参照する方法について解説しました

2つの株価は、上記では手動にて参照しましたが、次の章ではカスタム列・作成画面から同じロジックを自動で適用できるようにします

カスタム列・作成画面で連番を操作

前行参照

前章にて、手動で行ったロジックをカスタム列・作成画面にて適用すると下の図のような数式になります

こちらも数式が<前ステップ名 + 角括弧 + 波括弧>となっています

違うのは、波括弧 / {}内に「ⅰ:直接インデックス列」を挿入した上で「ⅱ:1を引く」という2点です

結果、エディタ内に次の画像のように「前行を参照した列」が挿入されます

ちなみに、以上のようなカスタム列を挿入する前に、次の画像のように数式バーを前の状態に戻しておきましょう

差額計算

これで前行を参照する仕組みができました

次に、カスタム列作成画面を「差額」が計算できるように修正します

こちらは、下の画像のように「株価」の列を挿入して「前行」を引くだけです

これで、差額を算出した列が挿入されます

エラー修正

今のままだと「差額」の列の1行目がエラーになってしまします

ですので、IF式をカスタム列作成画面に組み入れてエラーを回避します

IF式は「IF 条件式 then ~条件に合う場合~else~条件に合わない場合~」と書きます

条件式は「インデックス列」が0の場合を条件にします

これで、1行目のエラーを回避できました

<まとめ>

 今回は、前行を参照して「前行との差額」を算出する方法を解説しました

 エディタ内で、インデックス列を挿入すると自動的に「0」から始まる連番が挿入されます

 この連番をレコードの番号に置き換えて、前行を参照する仕組みに応用しました

 実際に作業としてはカスタム列・作成画面にて、角括弧の中に「インデックス列」を挿入して活用しました

画像に alt 属性が指定されていません。ファイル名: カスタム列作成画面-1.jpg

 今回紹介した「前行参照」が行えると、分析用の集計作業で行えることの幅が広がります

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

長文を最後まで読んで頂き、誠にありがとうございました

参考までに、今回使用したファイルを添付します

次回は「例外処理」について解説します

https://analytic-vba.com/power-query/m-code/begin-error-handle/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

【M言語に慣れる】_14回目_複数行を1つのセルにまとめる

【M言語は難しくない】今回は複数行に拡散している値を、次のGIF画像のように「記号」をつなぎ目にして、1つのセルにまとめます

目次

今回のポイント

今回使用するデータ

グループ化の実施

カスタム列の追加

<まとめ>

今回のポイント

今回のポイントは2つあります

1つ目は過去記事で紹介した「すべての行」によるグループ化です

この「すべての行」によるグループ化により、グループ別にテーブルを作成します

2つ目はリストの「値の抽出 / 1つのセルにリスト化」機能の活用です

こちらは、実際の例で見てみましょう!

空のクエリで次の画像のように、2つのリストを作成したとします

上の画像のリストを一旦、テーブル化した後、黄色に印を付けた「展開マーク」をクリックをすると「値を抽出する」が選択できます

上のGIF画像のように「値を抽出する」を選択した後は、リストの「区切り記号」を指定できます

「区切り記号」を指定した後は、下の画像のように1つのセルにリストの値が出力されます

今回使用するデータ

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

名前の列をグループ化し、グループ毎に「商品列に含まれる商品」を1つのセルに出力します

グループ化の実施

今回の解説は、使用データをエディタで開くところから始めます

上の画像にて、黄色の印を付けた「グループ化」をクリックします

なお、事前に「注文日」の列は削除しておきます

グループ化の内容は次の画像のように指定します

・グループ化項目:名前の列をグループ化します

・新しい列名:詳細

・操作:前述のように「すべての行」を指定します

・列:空欄のままでOKです

上記のように指定してOKボタンを押すと、エディタ画面は次のようになります

グループ化された名前毎に、テーブルが作成されています

次はグループ毎に、上の画像の商品列の内容をリスト化します

カスタム列の追加

前述のグループ毎にリスト化するには、カスタム列・作成画面を開くところから始めます

カスタム列・作成画面で、次の画面のように詳細列を指定すると、過去の記事の通りグループ毎にテーブルが作成されます

次の画像の「詳細.1」列が、上の画像から出力されたカスタム列の内容です

今回はテーブルではなく、各テーブル内の商品リストを出力します

リストを出力するには、カスタム列・作成画面にて次の画像のようにリストになる列を加えます

これで、商品リストが各テーブル毎に出力されます

各リストにカーソルをあてると「リストの中味」が次の画像のように見れます

こちらを前述の「今回のポイント」にて紹介したように、「展開マーク」をクリックし、「値を抽出する」から1つのセルにリストを出力します

値を抽出する」をクリックした後は次の画像のように、値を区切る記号を指定する画面が出てきますので、そちらで「記号」を指定してください

記号を指定したら、次の画像のようにグループ毎に1つのセルにリストが出力されています

<まとめ>

今回は複数の行の内容を、1つのセルに出力する方法を解説しました

 ポイントは2つあり、まず1つ目はグループ化において「すべての行」を指定することです

 この「すべての行」を指定すると、グループ毎にテーブルが作成されます

 グループ毎にテーブルを作成したら、「カスタム列・作成画面」にて各テーブルからリストを作成しておきます

 2つ目のポイントは、各リストの内容を「値を抽出する」機能にて1つのセルにリストを出力することです

今回紹介した方法は、それほど使用頻度は高くないと思います

 ただ、今回の内容は、M言語の重要概念であるテーブルとリストを体感するのにいい内容だったと思います

 特にグループ化において、「すべての行」を指定してグループ毎にテーブルを作成するパターンは色々と応用できそうです!

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

記事を最後まで見て頂き誠にありがとうございました

参考までに今回使用したファイルを添付します

次回は、M関数のText関数について2つ事例を紹介します

https://analytic-vba.com/power-query/m-code/begin-text-function/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

【M言語に慣れる】_15回目_TEXT関数/文字列関数

【M言語は難しくない】今回は、TEXT関数の中から、2つの事例を紹介したいと思います。通常のエクセル関数と同じ様に、M言語でも文字列を操作する関数は多数あります。興味のある方はぜひ、こちらのMicrosoft社のページも参照してみてください

目次

文字列の有無を判定する関数

文字列を置き換える関数

<まとめ>

文字列の有無を判定する関数

 Text.Contains関数は、ある文字列の中に指定した文字列があるかとうかを判定し、「TRUE」か「FALSE」で返す関数です

以下の一文は、Microsoft社のページからの抜粋になります

Text.Contains(“Hello World”, “Hello”)

上の ように記述した場合には、「TRUE」を返します

次もMicrosoft社のページからの抜粋になりますが、この場合は「FALSE」を返します

Text.Contains(“Hello World”, “hello”)

ちなみに、

 M言語では、大文字と小文字の違いは考慮されますので、この点は注意が必要です

では、実際にPower Queryエディタ(以降、エディタ)で実際にこの関数を操作してみます

使用するデータは、「抽出」という1列だけがあるこちらのデータになります

 上の画像のデータから、Text.Contains関数で文字列「0」を含む行を判定し、「TRUE」もしくは「FALSE」を返してみます

まず、「列の追加」タブからカスタム列・作成画面を開きます

こちらの画面でカスタム列の式に「Text.Contains」と入力します

上のGIF画像のように「Text.Con・・・」と入力する途中で、該当の関数が出てきますので、Tabキーで確定します

次にText.Contains関数の丸括弧の中を、以下の画像のように確定します

すると、エディタ内に「0」が含まれる行を判定した列が追加されます

文字列を置き換える関数

 Text.Replace関数は、ある文字列の中から、指定した文字列を発見して置き換える関数です

以下の一文は、Microsoft社のページからの抜粋になります

Text.Replace(“the quick brown fox jumps over the lazy dog”, “the”, “a”)

この場合、返される文字列は次のようになります

“a quick brown fox jumps over a lazy dog”

上記の場合、”the”が”a”に置き換えられています

 では、Text.Contains関数と同じデータを使用して、「0」を「a」に置き換えて見ましょう!

 こちらもText.Contains関数と同じ様に、カスタム列作成画面からM関数を使用します

上の図では、カスタム列作成画面に次のような式を入力しています

=Text.Replace([抽出],”0″,”a”)

では、上記の式を入力してカスタム列作成画面のOKボタンを押します

すると、エディタ内に文字列が置き換えられた列が追加されます

<まとめ>

今回は、Text関数の中から2つだけ事例を紹介しました

 1つ目は、文字列が含まれるかどうかを判定する「Text.Contains関数」でした

2つ目は、文字列を置き換える「Text.Replace関数」でした

 もし、通常のメニューにない操作をM関数で行う場合には、前述のMicrosoft社のページをぜひ参照してみてください

 ちなみに、カスタム列作成画面でM関数を入力する際に、自動で候補の関数が表示されます

 該当の関数を選択した場合、Text関数の場合であれば「TextText」と重なってエラーになるケースがあります

こちらのエラーについては、注意が必要です

今回の解説は以上となります

長文を最後まで読んでいただき誠にありがとうございました

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

にほんブログ村