カテゴリー別アーカイブ: 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言語に慣れる_2回目~カスタム列~

【M言語は難しくない!】今回は、前回紹介したM言語の特徴を意識しながら、実際に「M関数」を使用したカスタム列を作成してみましょう!

では、前回紹介したM言語の特徴を振り返ってみましょう!

ⅰ.コードのカラー

赤は「文字列」、青はeachなどの「予約語」、緑は「自動記録された値」です

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

M言語は、大文字と小文字の違いを認識します

ⅲ.ステップの引継ぎ

適用したステップは「ステップ名」を通じて、前ステップの内容を引き継ぎます

今回はこの3つの特徴に加え、次の4つ目の特徴も意識しましょう!!

ⅳ.データ形式

 エクセルは表計算ソフトであって、Accessのようなデータベースソフトではありません

 よって、過去の記事で紹介したように、Power Queryではデータ形式を意識した「データの構造化」がエディタを開く際に、自動的に行われます

 当然、Power Queryエディタ(以降、エディタ)内で列を追加する時にも「データ形式」を意識する必要があります

 では、今回意識すべきPower Queryの4つの特徴を確認したところで、M関数を使用した「カスタム列」の追加についての解説を始めたいと思います

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

ファイルデータはこちらになります

アイコン

Mコード2回目_練習 21.42 KB 31 downloads

...

こちらのデータから、次の列をM関数を使用して作成します

A.「姓」と「入会年」を組み合わせたログインID

「入会年」は「整数」のため、M関数を使用して「文字列」に変換します

B.「入会年」「入会月」と「1日」を組み合わせた入会日

M関数にて、3つの整数から日付を作成します

エクセル関数のDATE関数と同じ要領です

なお、上のABの列を作成する前に、ウォーミングアップとこれまでの復習を兼ねて、簡単なカスタム列を作成してみましょう

1.全ての行が「1」の列

まず、データテーブル上で右クリックし、下の画像の黄色の箇所からエディタを開きます

エディタが開いたら、「列の追加」タブから「カスタム列」をクリックします

次に開いた「カスタム列」の画面で「1」を指定します

上の画像のように「1」を指定したら、右下のOKボタンをクリックします

すると全ての行が「1」になっている新たな列「1」が追加されます

では、エディタ画面上の数式バーの中味を詳細に確認してみましょう

まず、()の中の左から1番目の「変更された型」についてです

①ステップ名

こちらは、前ステップの「変更された型」を引き継いでいます

②新しい列名

文字列”1”としてで表示されています

③システム予約語

「each」はシステム予約語なので、で表示されています

④自動記録された値

最後の”1”は自動記録された値なので、で表示されています

①から④まで数式の中味を確認しました

ここで、数式バーに「1」と入力してみましょう

すると、③のeachの意味合いがよく分かると思います

数式バーに1と入力すると「1」が1つだけ表示されます

ですので、「each」というシステム予約語により「それぞれ~」「各々~」のような意味になるのが御理解頂けたと思います

2.ログインID の作成/文字列への変換

2.ログインIDの作成では、記事の冒頭のA.で前述したように「姓」と「入会年」を組み合わせ、ログインIDの列を作成します

なお、「姓」と「入会年」を組み合わせる際には「」を使います

では、1.でも行ったように「カスタム列」の画面から作成を行います

「姓」と「入会年」は、ともに既存の列なので、画面右から挿入します

次の画像の内容で式を記入したら、画面右下のOKボタンをクリックします

ちなみに、上記の内容であれば、画面左下にエラーメッセージは出ません

ところが、出力された新たな列は、全てエラーになります

このエラーが何故発生するかというと、記事の冒頭ので紹介したように、M言語では「データ形式」を意識する必要があるからです

「姓」は「文字列型式」ですが、実は「入会年」は「整数」です

ですので、「入会年」は文字列に変換する必要があります

ここで、M関数を使用します

エクセル関数では次の画像の画面から、該当の関数を探すことができます

M関数でも同じような画面があります

上の画像の「Power Queryの式についての詳細」をクリックすると、下の画像のMicrosoft社の画面に遷移します

上の画像の下に「カテゴリ別の関数」とありますが、こちらの中に「テキスト関数」という関数があります

更に、こちらの「テキスト関数」の中を下にスクロールすると、「Text.From」という関数があります

こちらの関数で「入会年」を「整数」から「文字列」に変換できます

このText関数では、変換対象をText.Form()の”()“の中に入れます

なお、

関数は下の画像の数式バーに直接入力してしまいましょう!

Text関数を使用する際、「Text.Form」の中のTとFのいずれかを小文字にするとエラーになりますので、注意して入力しましょう!

3.入会日の作成/整数から日付作成

こちらは、記事の冒頭のB.にて紹介した、下の画像の「#date」関数を使用した内容になります

こちらのM関数は、#date()()の中に、整数の「年」「月」「日」を設定します

下の画像のエクセル関数の「DATE関数」と同じ要領になりますので、こちらのM関数は取り組みやすいと思います

但し、2.で扱ったText.From関数と違い、この関数は小文字dで始まります

この点については、エラーにならないように注意して入力しましょう!

作成する画面についてですが、こちらは、カスタム列の作成画面で作成します

()の中の最後の「日」は手動で「1」を入力します

新たに作成された列は、次の画像のように出力されます

<まとめ>

今回はカスタム関数の作成画面にて、3つの列を作成しました

1.全て1の列

2.文字列/姓と整数/入会年を組み合わせたログインID

3.整数/「年」「月」「日」を組み合わせた入会日

 1.では、前回、M言語の特徴として紹介した「each」などのコードカラー()についても解説を行いました

 ちなみに、「each」はM言語を扱う際には頻繁に出てきますので、感覚として慣れて置いた方が良いです

 2.では、入会年をM関数のText.Fromを使用して「整数」から「文字列」に変換しました

 記事の冒頭でも紹介したように、M言語では「データ形式」を意識する必要があります

 M言語を扱う際には、今回のようにデータ形式を変換する場面が出てくるはずですので、Text.From関数はぜひ習得しておきましょう!

最後は、#date関数を使用して「入会日」を作成しました

こちらの関数はText.From関数と違い、小文字から始まります

 前回もM言語の特徴として解説しましたが、M言語では大文字と小文字を区別する必要があります

 筆者も何度か、この大文字と小文字の区別を間違えてエラーを出しましたが、意外とエラー原因が些細すぎて原因に気づかないものです

この「大文字と小文字の区別」については特に注意しましょう!

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

参考までに今回使用したファイル(完成版)を添付します

 エディタ画面の「適用したステップ」の表示が、解説に使用した内容とは若干相違がありますので、この点はご了承をお願いします

アイコン

Mコード2回目_完成 24.89 KB 14 downloads

...

では、次回もよろしくお願いします

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

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

にほんブログ村

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

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

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

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

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

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

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

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

目次

今回のポイント

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

演習1/受注金額1位の担当者を抽出

演習2/最高値の半分未満の担当者抽出

<まとめ>

今回のポイント

ステップ名変更

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

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

参照ステップ

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

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

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

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

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

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

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

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

演習データ

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

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

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

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

演習1/受注金額1位の担当者を抽出

ステップ名を変更

まず、表をテーブル化してエディタを開きます

表の上で次の画像のように右クリックし、「テーブルまたは範囲からデータを取得」をクリックします

エディタが開いたら、「今回のポイント」で前述したステップ名の変更を行います

下の画像の「変更された型」を「DATA」に変更しましょう

クエリ名を変更するのと同じ要領でステップ名も変更できます

「変更された型」の上でF2キーを押せば、下のGIF画像のようにステップ名を変更することができます

ステップ名を変更

なお、前ステップを参照する時に「#”前ステップ名”」と自動的に表示される時があります

この点は後述します

参照ステップを作成/全体最高値の算出

次に、受注金額の列を選択して「変換タブ」の「統計」から最大値をクリックします

すると上のGIF画像のように、受注金額の最大値が算出されます

ここでも、ステップ名は変更しましょう

この時点では、適用したステップの欄は次の画像のようになっています

最後のステップの「計算された最大」を「Cal-Max」に変更しておきましょう

さて、

ここから、「Cal-Max」を参照ステップとして確定する処理をします

確定処理のために、下の画像の数式マークをクリックします

すると、「Cal-Max」のステップが1つ繰り上がります

上のGIF画像のように「Cal-Maxステップ」の後に「カスタム列1」というステップができます

追加された「カスタム列1」ステップの数式バーは下の画像のように、最大値を算出してある前ステップを参照しています

なお、数式バーが「Cal-Max」ではなく「#”Cal-Max”」というステップ名になっています

こちらは、前述の「ステップ名の変更」でも少し触れましたが、ステップ名が空欄やーなどの記号で文字列が繋げられている場合、#”ステップ名”になります

この点は、必ず意識しておきましょう

「最高値」の担当者を抽出

この<受注金額が「最高値」の担当者を抽出>では主に、3つのことを行います

①前ステップをスキップ ➡ ②最高値をフィルタリング ➡ ③シートへの読込

上記の3つを通じ、前述の参照ステップを活用してダイナミックにフィルタリングを行えるようにします

①前ステップをスキップ

参照ステップを確定した段階では、エディタ内の表示は「Cal-Max」ステップで算出した「最高値」だけが表示されています

最終ステップの「カスタム1」ステップの数式バーの内容を、1つ前の「Cal-Max」をスキップした、「DATA」ステップの参照に変更します

これで、上のGIF画像のようにエディタの表示が、参照ステップを確定する前の表示に戻りました

②最高値をフィルタリング

では、記事の冒頭でも紹介したように、まずはダミー値で受注金額をフィルタリングします

受注金額をフィルタリングするには、まずは下の画像の▼マークをクリックします

それから、下の画像の黄色の箇所にダミーとして、表示されている金額の内の「どれか1つ」を入力します

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

但し、上の画像の右側にある、緑色で自動記録された「50000」はあくまでダミーです

こちらの「50000」を参照ステップの内容に書き換えます

前述したように、#”ステップ名”にしないとエラーになりますので注意が必要です

これで、フィルタリングがダイナミックになったので、エクセルシートに読込みます

③シートへの読込

エクセルシートに読込む前に、担当者名以外の列は削除しておきます

下のGIFが、エクセルシートに読込んだ時の内容です

下の図の左、元データが降順で並んでいるので、受注金額が最高の担当者が正しく抽出されていることがよく分かると思います

では、元データに新たに「既存の最高値」を超える担当者/TESTを加えてみます

そして、エクセルシートに読込んだクエリを更新します

きちんとダイナミックフィルタリングができていることが、上のGIFで確認できました!

演習2/最高値の半分未満の担当者抽

こちらについては、演習1で作成したクエリを複製し、フィルタリング条件を変更するだけで完成です

クエリ複製

エディタ内で、演習1で作成したクエリの上で右クリックし、複製をクリックします

ちなみに、上の画像にあるように演習1のクエリは「Max-Member」というクエリ名で作成してあります

フィルタリング条件変更

前述のクエリの複製を行ったら、下の画像のようにクエリ名をF2キーで変更しておきましょう

クエリ名を変更したら、クエリを開き次の画像の「フィルターされた行」ステップをクリックします

こちらの数式バーにある、条件式の部分を変更します

演習2でフィルタリングに設定する条件は「最高値の半分未満」ですので、上の画像の赤丸部分を次の画像のように変更します

符号についても、変更を行うのがポイントです

シートに読み込み

フィルタリング条件を変更したところで、読込処理を行います

読込先は演習1の下にします

最高値は担当者/TESTの120,000なので、最高値の半分/60,000未満の担当者が正確に抽出されています

では、最高値を130,000に引き上げてみましょう!

そうすると、受注金額が60,000の担当者も抽出対象になるはずです

クエリを更新すると、下のGIFのように受注金額60,000の担当者があらたに抽出されています

これで、演習2で作成したクエリもダイナミックフィルタリングが行えていることを確認できました

<まとめ>

 今回は、前々回に解説した「参照ステップ」をフィルタリングに活用してダイナミックフィルタリングを行うクエリを2つ作成しました

演習1のクエリでは、「最高値」の担当者を自動で抽出できるようにしました

演習2のクエリでは、「最高値」の半額未満の担当者を自動で抽出できるようにしました

 2つのクエリともに、数式バーに記録されたM言語を編集するだけでフィルタリングをダイナミックなものにしました

 今回の解説を通じて、M言語を学習する効果の大きさを感じて頂けたら幸いです

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

次の回は、カスタム列・作成機能の裏技を使用して、特殊なテンプレートを作成します

https://analytic-vba.com/power-query/m-code/begin-merge-table/

参考までに今回解説したファイルの完成版を添付します

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

にほんブログ村

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

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

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

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

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

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

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

勤務表

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

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

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

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

・勤務日のリスト ➡ TimeTable

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

目次

今回のポイント

データ形式の事前調整

各行に日付テーブル作成

作成テーブルの展開

条件式の追加による調整

シートへの読み込み処理

<まとめ>

今回のポイント

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

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

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

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

データ形式の事前調整

前述の通り、解説はエディタ内に2つのクエリがある状態から開始します

・担当者名/列のリスト ➡ Namesクエリ・勤務日/列のリスト ➡ TimeTable

過去の記事で解説した通り、「データの構造化」が行われた結果、データ形式の変更が必要な箇所が1点でてきますので、まずはそちらを変更します

下の画像の勤務日を「時刻」から「日時」に変更します

データ形式の変更は、上の画像の左上の黄色の印から行えます

上のGIF画像のように、日付を指定した後は次の画面が開きます

「現在のものを変換」をクリックすれば、下のGIF画像のように型式変更が行われます

各行に日付テーブル作成

では、「Names」クエリをエディタ画面の左から開いてください

次に「列の追加タブ」から「カスタム列」をクリックしてください

カスタム列の作成画面が下の画像のように開いたら、「新しい列名」を「勤務日」と指定してください

ところで、上の画像の右に「使用できる列」の欄があります

こちらには「担当者名」しか使用できる列がありません

挿入したいのは、勤務日であり、しかも担当者1人につき「1対多」の関係で結合する必要があります

ここからはM言語の出番です

M言語を書くといっても、実は、下の画像のようにクエリ名を指定するだけです

しかも、クエリ名を途中まで入力したところで、「入力補助」機能が働きます

クエリ名を指定したら、カスタム列作成画面の右下にあるOKボタンをクリックします

すると、記事の冒頭の「今回のポイント」で紹介した、テーブル結合が行われた画面になります

作成テーブルの展開

ここからは、中級編で解説したテーブル結合時の「展開処理」と一緒です

勤務日の右上のマークをクリックすれば、TimeTableクエリの内容が展開します

実際に上の画像のマークをクリックすると、実際には、下のGIF画像のように展開内容を指定する画面が開きます

こちらの画面では、展開項目として「勤務日」を指定するのはもちろんのこと、「元の列名をプレフィックスとして使用します」のチェックは外しておきます

こちらのチェックを外しておかないと「元のクエリの列名」も入ってしまいます

「元の列名をプレフィックスとして使用します」 のチェックを外したところで、画面下のOKボタンを押します

条件式の追加による調整

前述のテーブル展開を行った状態だと、下の画像のように、厳密には担当者名と勤務日が「1対多」の関係になっていません

上の画像の黄色に印をつけた箇所が、空欄になっていないといけません

こちらについては、黄色の箇所が空欄になる条件列を挿入します

勤務日は「2021/03/01」~「2021/03/03」の並びを繰り返しています

ですので、条件列は次の2つの内容になります

・勤務日が「2021/03/01」 ➡ 担当者名

・勤務日が「2021/03/01」以外 ➡ null(空欄)

では、上の2つの内容を、実際の条件列・作成画面に反映させます

条件列は、「列の追加」タブから「条件列」をクリックして作成画面を開きます

開いた作成画面に、前述2つの内容を設定していきます

まずは勤務日が「2021/03/01」の時の内容です

条件部分の「値」は「2021/03/01」を手入力します

あるいは、カレンダーから日付を選択することもできます

上の画像右の出力の箇所は、「列名」を選択できように、下の画像の「列の選択」を指定するのを忘れないようにしてください

次は 勤務日が「2021/03/01」以外の時の内容です

2点の内容を設定してOKボタンを押すと、エディタ内に新たな列が追加されています

シートへの読み込み処理

エクセルシートに読込む際には元の担当者名の列は削除し、追加した条件列を一番左に移動しておきましょう!

また、上の画像の「勤務日.1」の列は列名を「勤務日」にし、データ形式も「任意」/下画面から「日付形式」/下画面に変更しておきましょう!

では、実際にエクセルシートに読み込み処理を行います

<まとめ>

 今回は、M言語を1つ入力するだけで特殊なテンプレートを作成する方法について解説を行いました

 カスタム列の作成画面にて、クエリ名/TimeTableを指定するだけで「1対多」の関係によるデータ結合が行えました

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

その後は、通常のデータ結合時と同じように結合内容を展開しました

画像に alt 属性が指定されていません。ファイル名: Translate-TO-Table2.gif

今回は、上記の仕組みを使用して「担当者名」と「勤務日」のリストから勤務表を作成しましたが、他にも活用できる場面があるはずです

何より、M言語の威力を肌で感じて頂けたら幸いです

今回の解説は以上です!!

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

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

次回は、M関数の参照機能について解説します

https://analytic-vba.com/power-query/m-code/begin-ref/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 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言語に慣れる_7回目~空のクエリからテーブル作成~

【M言語はむずかしくない!】今回は、空のクエリから、3種類の括弧を使ってテーブルを作成します。テーブルを作成した後は、M関数も作成を行います!その際、M関数を使いやすくする方法についてもあわせて解説します!!

前回は2種類の括弧を使い、エディタ内で各種参照を行いました

角括弧 / [] ➡ リスト、列の抽出

波括弧 / {} ➡ レコードの抽出

前回で、M言語における2種類の括弧の役割がおぼろげながら見えてきたと思います

 今回、3種類の括弧を使ってテーブルを作成した後は、過去に紹介した方法とは別の方法でM関数を参照する方法についても解説します!

3種類の括弧に慣れると、M言語がグンと理解し易くなりますし、応用も効きます!

ぜひ、実際に手を動かして取り組んでみてください

まずは、空のクエリを作成するところから行います

なお、

他のクエリも後程参照したいので、演習を行う際には、既存クエリが存在するファイルを使用してください

目次

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

リスト作成

レコード作成

テーブル作成

M関数の用法を参照・作成/列名変更

<まとめ>

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

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

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

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

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

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

リスト作成

リストは波括弧 / {}を使用して作成します

①値が1個

では、数式バーに={“a”}と入力してみます

リストができ、値がリストに1つ入りました

②値が2個

では、={1,2}と入力してみます

リストができ、値がリストに2つ入りました

③値が10個

こちらは、実際に使う機会がないとは思いますが、エディタ内でデータの構造化が行われていることを肌で感じてもらうために紹介します

={1..10}と入力すると、1から10まで自動的に値が作成されます

④リストが2個:各値1個

波括弧の中にコンマ / ,を介して2つ波括弧を入れると、リストが2個できます

入力はこちらのように入力します

= {{“a”},{“b”}}

上の画像の上下どちらかの”List”をクリックすると、適用したステップ画面に「ナビゲーション」が追加されます

上のGIF画像を見ると、ファイルやフォルダからデータを取得する時に出てくる「ナビゲーター」画面の意味合いが良く分かると思います

⑤リストが2個:各値2個

入力はこちらのように入力します

= {{“a”,”b”},{1,2}}

上の画像の、下のリストをクリックすると、下の画像のようにリスト内容が表示されます

レコード作成

角括弧 / []を使用して作成します

①値1

こちらは=[見出し1=値1]と入力します

①値2

こちらは=[見出し1=値1, 見出し2=値2 ]と入力します

レコードは行方向で通常並んでいますが、今回のように数式バーに直接入力した時は、表示のされ方が列方向になります

テーブル作成

テーブルを作成する場合には、これまでの解説で登場しなかった丸括弧 / ()も使用します

そして、丸括弧のまえに=#tableをつけてます

=#tableとコードを書くことにより、丸括弧 / ()内の内容がテーブルになります

ちなみに#の後に続く、tは小文字のtですので、その点は注意しておきましょう!

①1列:値1

こちらは、=#table({列名1},{{値1}})と入力します

②1列:各列値2

こちらは、= #table({列名1},{{値1},{値2}})と入力します

③2列:各列値2

こちらは、

= #table({列名ⅰ,列名ⅱ},{{ⅰ値1,ⅰ値1},{ⅱ値2,ⅱ値2}})

波括弧の組み合わせが多くなので、上の内容をコピーして修正してみる方法でもいいかもしれません

M関数の用法を参照・作成/列名変更

M関数参照

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

他のクエリが開いたら、どの列でもいいので、列名変更を行ってみます

すると、数式バーには「Table.RenameColumns」というM関数が表示されているはずです

表示を確認したら、M関数の丸括弧の部分を、下のGIF画像のように全部削除してください

すると、下の画像のようにTable.RenameColumns関数の解説が表示されます

上の画像の解説画面を下にスクロールしていくと、使用例が出ています

これは「CustomerNum」という列名を「CustomerID」に変えるという使用例です

上の画像の使用例には、黄色の印が2か所あります

黄色の箇所の間には、もう一つM関数が入っています

こちらは、通常のテーブルの内容に置き換えておきます

黄色の間の箇所を、置き換えた内容が以下です

Table.RenameColumns(
テーブル名,
{列名変更前, 列名変更後}
)

こちらの内容をコピーして活用し、先ほど空のクエリにて作成したテーブルの列名を変更しましょう!

M関数作成

まずは、「Table.RenameColumns(」の部分を、空クエリで作成したテーブルの前にコピーします

そして、残りの箇所を上の画像の後ろにコピーします

コピーする時には、3行に分かれている内容を1行に集約しておいてください

,
{列名変更前, 列名変更後}
)

コピーした後の画像がこちらです

こちらを既存の列名/氏名と新列名/名前に置き換えます

置き換えた後は、下の画像のように「氏名」の列名の列が「名前」に変更されています

<まとめ>

今回は、大きく分けて2つのことを行いました

 1つ目は、3種類の括弧を使用して、空のクエリにて「リスト」「レコード」「テーブル」を作成しました

 2つ目は、空のクエリで作成した「テーブル」にて列名を変更するM関数を使用しました

 M関数を使用する前に、他クエリで列名変更を行い、記録されたM関数から解説を表示しました

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

そして、こちらの使用例をカスタマイズして、M関数を使用しました

 今回は2回に分けて、3種類の括弧について解説しましたが、括弧の意味・役割をおぼろげながらでも理解できていると、紹介した使用例なども活用しやすくなります

ぜひ、今回の解説を機に3種類の括弧に強くなっていきましょう!

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

次回は「each」コードについて慣れていきましょう!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 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へ

にほんブログ村

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へ

にほんブログ村