【M言語は難しくない】今回は、M関数を使用して連続した日付を作成する方法を解説します。２つ連続した日付を作成する方法を解説した後に、ダイナミックに日付を作成する方法を解説します。下のGIF画像のように、ある表の最小・最大日と連動させて、連続した日付が入った表を作成する方法を解説します。
上のGIF画像では、画像左の最大値を「2021/04/04」から「2021/04/06」で入力し直しています
その後、「更新処理」を行います
すると、画像右にある表の最終行が「2021/04/04」から「2021/04/06」に拡大します
この回は、過去の記事で解説した波括弧：{}を使用した「連続データを作成する技術」をフルに活用します！
目次
今回のポイント
今回使用するデータ
連続した日付の作成
ダイナミックに連続データを作成
＜まとめ＞
今回のポイント
今回も前述のように、過去記事で紹介した括弧の使い方が最大のポイントになります
後、これまで紹介してこなかったM関数も使用します
括弧の使い方
過去記事にて、１から１０まで連続した数字を空のクエリに作成する方法を解説しました
今回はこの波括弧 / {}（最小..最大）から作成される連続データを使用して、連続した日付を作成します
フィルタリング
日付が入ったテーブルは下の図のようにフィルタをかけて、最小日と最大日は特定できるようにしておきあす
上の図のようにフィルタリングを行うと、下の図のようなテーブルができます
1行目が最小日、2行目が最大日です
こちらは、連続した日付を作成するときに使用します
M関数
今回使用するM関数はこちらです
事前に概要を紹介しておきます
「年、月、日」から日付を作成します
「日、時間、分、秒」から期間値を作成します
「開始日、リスト個数、増分」から日付のリストを作成します
＃dateと#durationと組み合わせて作成します
値を数値型式にして返します
今回使用するデータ
今回は、記事の冒頭でも前述したように、まずは２つ連続した日付を作成する方法を解説します
その後、次の画像のデータを使用します
上の画像の「最小と最大の日付」を変えたら、連動して連続した日付を入れた表が作成されるようにします
連続した日付の作成
これから２つ作成方法を解説しますが、どちらもまずは空のクエリから作成します
エクセル画面の上にある「データタブ」➡「データの取得」の順でクリックします
その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします
そして、下の画像にある「空のクエリ」をクリックします
するとエディタが開き、空のクエリが立ち上がります
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関数の便利さに触れてみてください
記事を最後までお読み下さり、誠にありがとうございます
参考までに今回使用したファイルを添付します
にほんブログ村
