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

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

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

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

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

目次

今回のポイント

今回使用するデータ

連続した日付の作成

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

＜まとめ＞

今回のポイント

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

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

括弧の使い方

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

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

フィルタリング

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

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

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

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

M関数

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

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

#date

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

#duration

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

List.Dates

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

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

Number.From

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

今回使用するデータ

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

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

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

連続した日付の作成

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

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

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

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

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

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つ目」として解説した方法は、次の数式を空のクエリの数式バーに入力するところからはじめました

={最小日..最大日}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

これで完成です

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

＜まとめ＞

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

にほんブログ村