タグ別アーカイブ: M

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 ]と入力します

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

以降は有料ページ/課金サービスへ

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言語に慣れる_10回目~グループ毎に連番作成~

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

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

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

まさに魔法です

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

目次

今回のポイント

今回の使用データ

グループ化の実施

M関数の入力

<まとめ>

今回のポイント

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

グループ化

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

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

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

M関数作成

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

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

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

今回の使用データ

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

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

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

以降は有料ページ/課金サービスへ

【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言語では、大文字と小文字の違いは考慮されますので、この点は注意が必要です

以降は有料ページ/課金サービスへ

不規則に散らばっているデータを一括取得~M関数~

 今回は上の画像のようにデータが複数シートに散らばっており、しかも行見出し位置/ヘッダー行不規則なデータを一括で取得します

今回の最大のポイントは、List関数で行見出しの位置を取得する点です

そこさえクリアすれば、意外と簡単にできます

では、今回使用するデータと行いたい事の確認から解説を始めます

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

今回使用するのは次のファイルです

このファイルの中には3つのシートがあります

但し、前述のように3つのシートそれぞれの行見出し位置が違います

 2020年1月のデータは1行目から始まっていますが、2月のデータは2行目から始まります

そして、3月のデータは3行目からです

 この3つのシートからM関数を組み合わせてデータを一括で取得するのが、今回行いたい事です

今回のポイント

 今回の最大のポイントは、前述のようにList関数で行見出し位置を取得することです

 行見出し位置を取得するには、行見出しにある「客先コード」をキーにして、M関数「List.PositionOf」を使います

List.PositionOf関数の他にも2つM関数を組み合わせます

List.PositionOf

List.PositionOf関数はリストの中から、指定の値の位置を取得します

書き方は、次の通りとなります

List.PositionOf(リスト名,取得したい値)

ここで、注意点が1点あります

M言語は0ベースである点です

例えば、List.PositionOf関数を使用すると、次の画像の「客先コード」の位置はではなく「0」とでます

これは、M言語が0で始まる仕組みになっているからです

Table.RemoveFirstN

Table.RemoveFirstNは、テーブルから指定した行数を削除するM関数です

書き方は次のように書きます

Table.RemoveFirstN(テーブル名, 削除する行数)

Table.PromoteHeaders

Table.PromoteHeadersは、1行目を見出しに昇格させるM関数です

Power Queryエディタ内にも同じメニューがあります

今回の内容は、後述しますがM関数で行った方がベターです

このM関数の書き方は、次の通りとなります

Table.PromoteHeaders(テーブル名)

今回のポイントである、3つのM関数の内容を確認したところで、本格的な解説をはじめます

ファイルの読込

まず最初に、前述のサンプルファイルの読込処理を行います

サンプルファイルとは別のファイルで、下の画像の処理を行います

 上の画面の「ブックから」で前述のサンプルファイルを指定した後、Power Queryエディタ(以降、エディタ)が開くと次の画像のような状態になっています

上の画像の左から2つ目の列にある「Data」が今回のポイントの一つです

 各行にある「Table」の文字の横をクリックすると、次のGIF画像のように中味が見れます

次からはこの「Data」列を活用して、カスタム列をM関数により作成します

カスタム列の作成

List.PositionOf関数で行位置を抽出

では、カスタム列・作成画面を開きましょう

 ここで確認ですが、行位置を取得する対象は下の画像の「客先コード」の文字です

 上の画像を見ると、「客先コード」にはⅰ)Data/Table➡ⅱ)Column1➡ⅲ)客先コードの順で指定するとたどり着けるようになっています

 カスタム列・作成画面でも、M関数/List.PositionOfでの引数設定時に上の流れを使用して指定します

上の画像のようにList.PositionOf関数の引数を次の様に指定します

一引数:[Data][Column1]

第二引数:”客先コード”

そして、カスタム列・作成画面の下にあるOKボタンを押すと次の画像のように客先コードの位置が出力されます

あくまでM言語は0から始まるので、その点についてはご注意ください

 1行目の内容では、客先コードは1行目にありますがM言語のベースに従って0と出力されています

Table.RemoveFirstNで不要な行の削除

 List.PositionOf関数により行見出しの位置は抽出できましたので、「行見出しの位置-1」分の行数をM関数/Table.RemoveFirstNを使用して削除します

Table.RemoveFirstN関数の書き方は、前述の通り次の通りです

Table.RemoveFirstN(テーブル名, 削除する行数)

今回は上の第一引数のテーブル名には、[Data]を入力します

そして、第二引数には上のList.PositionOf関数をー1をせずにそのまま設定します

 上の画像の通りに数式を指定してOKボタンを押すと、「本来、行見出しにするべき行/客先コードのある行」が次の画像のように1行目に来ています

但し、このまま作成されたテーブルを展開すると不都合な点が1点あります

 本来、行見出しにするべき行が行見出し/ヘッダーになっていませんので、このまま展開作業をすると不要な行見出しがデータとして入り込んでしまいます

ですので、もう一つのM関数で不要な行を削除できるようにします

Table.PromoteHeadersで見出し行を作成

では、最後の仕上げとして各テーブルの1行目を見出し行/ヘッダーにします

 こちらは、M関数/Table.PromoteHeadersの()の中にこれまでの内容を入れるだけです

Table.PromoteHeaders関数の内容を反映した結果が次の画像です

 上の画像の内容で、各テーブルを展開処理すればいいのですが、その前に既存の列は削除しておきましょう

「他の列の削除」が終了したら、後は各テーブルを展開するだけ終了です

<まとめ>

 今回は、List.PositionOf関数をはじめとする3つのM関数を組み合わせて、不規則なデータを一括で処理できるようにしました

最大のポイントは、不規則な行見出しの位置をM関数で取得する点です

 それさえできれば、後は機械的にM関数を組み合わせるだけで一括処理ができるようになりました

今回の題材は、M関数の魅力、そしてM言語の魅力を知るのにいい題材だったと思います

ぜひ手を動かして体験してみてください

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

参考までに、今回の内容の完成版のファイルを添付します

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

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

にほんブログ村

カスタム関数を自身で登録して使い回す2~上級編9回目

 今回はPower Queryでカスタム関数を作成した後、実際に作成した関数を呼び出す方法を解説します

まずは、前回の内容の「振り返り」から行いたいと思います

前回は、カスタム関数を作成する際の「ポイント」と「作成画面」について解説しました

 解説の対象となる、これから完成しようとしてる「カスタム関数」は次の画像の「販売単価」「製造原価」「販売個数」から「利益」を算出する関数です

 上の画像の数式バーにあるように、利益を算出するのに数式を入力していたところを、カスタム関数を呼び出すだけで「利益」を自動算出できるようにします

カスタム関数のポイントとしては、2点を解説しました

1点目は、中学時代に習った「方程式を思い出す」です

カスタム関数はこの方程式と同じ様な考えで作成します

そして、代入するのは「列の名前」になるのが特徴です

2点目は、カスタム関数の作成画面についてです

前回は、上の画面を開いたところまでを行いました

 では、この「詳細エディター」画面にてカスタム関数を設定するところから解説をはじめます

目次

1.関数の設定

2.カスタム関数の呼出し

<まとめ>

1.関数の設定

今回は、次の数式を詳細エディター上に関数として設定します

利益=(販売単価-製造原価)x 販売個数

ここで、詳細エディターへの設定方法を分かり易くするために、前回のポイントで解説した方程式を使って解説します

前回のポイントで、方程式の事例として解説したのは次の方程式です

Y=aX + b

上の方程式を詳細エディターに設定したとすると、次の画像のようなイメージになります

詳細エディター設定例

letの箇所は2段の設定になります

1段目:Y = (a,X,b) =>

2段目:ax + b

1段目で「変動する数」として使用する記号を、=を挟んで右辺と左辺に分けて設定した後に、「=>」の2文字で2段目に繋ぎます

そして、2段目では方程式の右辺を設定します

inの箇所はYを設定するだけです

方程式を例に取って、詳細エディターの設定イメージをお伝えしたところで、本題の関数を設定します

利益=(販売単価-製造原価)x 販売個数

上の数式の「変動する数」はそれぞれ、以下のように設定するものとします

・利益 ➡profit

・販売単価 ➡price

・製造原価 ➡cost

・販売個数 ➡quantity

では、上の「変動する数」を詳細エディターにあてはめると下の図のようになります

画面下に以下の文字が出ていたら、右下の「完了」を押します

すると、下のような画像に切り替わります

以降は有料ページ/課金サービスへ