タグ別アーカイブ: #date

M言語に慣れる_2回目~カスタム列~

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

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

ⅰ.コードのカラー

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

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

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

ⅲ.ステップの引継ぎ

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

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

ⅳ.データ形式

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

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

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

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

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

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

こちらのデータから、次の列を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言語では大文字と小文字を区別する必要があります

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

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

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

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

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

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

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


にほんブログ村

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

【M言語は難しくない】今回は、M関数を使用して連続した日付を作成する方法を解説します。2つ連続した日付を作成する方法を解説した後に、ダイナミックに日付を作成する方法を解説します。下のGIF画像のように、ある表の最小・最大日と連動させて、連続した日付が入った表を作成する方法を解説します。

上のGIF画像では、画像左の最大値を「2021/04/04」から「2021/04/06」で入力し直しています

その後、「更新処理」を行います

すると、画像右にある表の最終行が「2021/04/04」から「2021/04/06」に拡大します

この回は、過去の記事で解説した波括弧:{}を使用した「連続データを作成する技術」をフルに活用します!

目次

今回のポイント

今回使用するデータ

連続した日付の作成

ダイナミックに連続データを作成

<まとめ>

今回のポイント

今回も前述のように、過去記事で紹介した括弧の使い方が最大のポイントになります

後、これまで紹介してこなかったM関数も使用します

括弧の使い方

過去記事にて、1から10まで連続した数字を空のクエリに作成する方法を解説しました

今回はこの波括弧 / {}(最小..最大)から作成される連続データを使用して、連続した日付を作成します

フィルタリング

日付が入ったテーブルは下の図のようにフィルタをかけて、最小日と最大日は特定できるようにしておきあす

上の図のようにフィルタリングを行うと、下の図のようなテーブルができます

1行目が最小日、2行目が最大日です

こちらは、連続した日付を作成するときに使用します

M関数

今回使用するM関数はこちらです

事前に概要を紹介しておきます

#date

「年、月、日」から日付を作成します

#duration

「日、時間、分、秒」から期間値を作成します

List.Dates

「開始日、リスト個数、増分」から日付のリストを作成します

#dateと#durationと組み合わせて作成します

Number.From

値を数値型式にして返します

今回使用するデータ

 今回は、記事の冒頭でも前述したように、まずは2つ連続した日付を作成する方法を解説します

その後、次の画像のデータを使用します

上の画像の「最小と最大の日付」を変えたら、連動して連続した日付を入れた表が作成されるようにします

連続した日付の作成

 これから2つ作成方法を解説しますが、どちらもまずは空のクエリから作成します

  エクセル画面の上にある「データタブ」➡「データの取得」の順でクリックします

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

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

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

M関数 / List.Datesで作成する方法

既に今回のポイントで解説済みの内容になります

前述の通り、#dateと#durationを組み合わせて作成します

では、以下の条件で連続データを作成してみます

・開始日 / 2021/05/01

・個数 / 5個

・増分 / 7日

関数は次のように記述します

=List.Dates(#date(2021,5,1),5,#duration(7,0,0,0))

上の関数を数式バーに入力すると、下のGIF画像のように2021年5月1日から始まる日付リストが「7日」おきに「5個」作成されます

波括弧 / {} を使用して作成する方法

前述の通り、波括弧 / {}(最小..最大)から作成された連番を利用して連続した日付を作成します

この方法の場合には、作成した連番を一旦テーブル形式に変換した後、日付形式に直すのがポイントです

ちなみに、エディタ画面からも「空クエリ」は作成できます

まず、下の画像のようにエディタ画面の左で右クリックします

その後に、上の画像の「新しいクエリ」右横にある▶マークをクリックします

すると「空のクエリ」を選択する画面がでます

さて、話を連続データの作成に戻します

まずは、次のように数式バーに入力して連続データを作成します

= {Number.From(#date(2021,4,1))..Number.From(#date(2021,4,30))}

数値型式を返す「Number.From関数」の中は、「#date関数」にて日付を作成しています

上の数式を入力した段階では、ただの「値」の羅列になっています

こちらはあくまで「リスト型式」なので、このままだと日付形式に変換できません

ですので、こちらをテーブルに変換します

すると次の画面のようになり、データ形式を指定できるようになります

こちらの画面で列名と合わせて、データ形式を直します

これで、連続した日付が作成できました

こちらのクエリは、後で表と連動させますので、クエリ名を「連続日付2」として保存します

この連続したデータを作成する方法の「2つ目」として解説した方法は、次の数式を空のクエリの数式バーに入力するところからはじめました

={最小日..最大日}

この数式は表と連動させてダイナミックに連続データを作成する際、応用して活用します

ダイナミックに連続データを作成

過去の記事にて、ダミーの値でフィルタリングしてステップを作成した後に、ダミーの値を後から置き換えて、「ダイナミックフィルタリング」を行いました

上の画像の赤印は一度、フィルターした値を「最高値」として算出した値/変数で置き換えています

今回は全く違うアプローチを行います

日付の最小値と最大値でフィルタリングした内容を、前述の「波括弧 / {} を使用して作成する方法」で作成した数式の中に組み込みます

では、日付の「最小値」と「最大値」の2つの値でフィルタリングを行いますので、まずは下の画像の元のデータをエディタで開きます

エディタを開いたら、まずはクエリを参照して別クエリ「担当者」を作成しておきます

こちらは、後でまたクエリの結合に使用します

そして、フィルタリングを行う前に「担当者」の列は削除します

「日付」の列だけ残したところで、フィルタリングを行います

フィルタリングは「カスタムフィルター」という仕組みを利用して、2つの値を「OR条件/また」にて指定できるようにします

上のGIF画像では、「日付フィルター」を選択した後に、新たに表示された列を最後までスクロールして「カスタムフィルター」を選択しています

こちらのカスタムフィルターの画面で「また」を選択した上で、2つの値を次の画像のように「最も早い」「最も遅い」と指定します

すると日付の「最小値」と「最大値」の2行が出力されます

これで日付の「最小値」と「最大値」のフィルタリングは終了です

一旦、クエリの名前 / 最小最大_日付を設定して「接続のみ」にて読込ます

次に、前述の連続データを作成したクエリを複製します

複製したクエリの「ソース」ステップを選択すると、数式バーには連続データを作成するための「最小値」と「最大値」が指定されています

上の画像の「最小値」と「最大値」を、1つ前で作成したクエリ「 最小最大_日付 」の1行目/最小値と2行目/最大値に置き換えます

置き換える際には、角括弧 / []波括弧 / {}を組み合わせて「クエリ名(テーブル名)[列名]{行位置}」の数式で指定します

上の画像だと分かりずらいと思いますので、以下に数式バーの部分は記述し直します

= {Number.From(最小最大_日付[日付]{0})..

           Number.From(最小最大_日付[日付]{1})}

上の式で最小最大_日付クエリの1行目と2行目をそれぞれ、0と1で表現しているのは、Power Queryが「0」をベースとしているからです

これで、表の値(元データ)に応じて連続データが最終ステップに作成されています

更にここから元データのクエリ「担当者」と日付をキーにして結合を行い、「担当者」の列を追加します

こちらは上の画像のように、「空欄/null」があるので、フィル作業を行います

変換タブの「フィル」にて下の空欄を上の値で埋めます

これで完成です

元の表と最小値と最大値を連動する仕組みも組み込まれています

<まとめ>

今回は、M関数を数種類使用して連続する日付データを作成しました

日付データを作成する方法は2種類解説しました

1つ目は、List.Dates関数を使用する方法です

こちらは、List.Dates関数の中に2つの関数を更に組み込みました

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

2つ目は、波括弧 / {}を使用して連続データを作成する方法です

こちらは、括弧の中に最小の日付と最大の日付を指定しました

= {Number.From(#date(2021,4,1))..Number.From(#date(2021,4,30))}

こちらの仕組みは、表をもとにしてダイナミックに連続データを作成する仕組みに応用しました

元の表をエディタで開き、日付の最小値と最大値をフィルタリングして、上の数式に組み込みました

= {Number.From(最小最大_日付[日付]{0})..

           Number.From(最小最大_日付[日付]{1})}

この組み込み処理により、ダイナミックな連続データが作成できるようになりました

今回はM関数が多く出て来たので、骨のある内容だったと思いますが、M関数の便利さも紹介できたと思います

ぜひ、実際に手を動かして、M関数の便利さに触れてみてください

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

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

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

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

にほんブログ村