Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

Excelのテーブルを元にPower Queryで日付テーブルを作る

More than 1 year has passed since last update.

Power BIだとDAXで手軽に日付テーブルを作れる。
しかしExcelだとPower Pivot標準の日付テーブルを利用するか、Power Queryで作る必要がある。

日付テーブルに必要なパラメーターやPower Pivotの操作を説明するのも結構面倒。
なのでExcelでテーブルを作り、そこで指定した日付範囲を基にPower Queryで日付テーブルを作るやり方を書きます。

最近仕事でExcelを使わざるを得ない事も多いので備忘も兼ねる。

日付でカレンダーを作る

Power Queryに取り込む準備

ワークシートに[元テーブル]という名前のテーブルを作り、列名は[年月最小値][年月最大値]とする。
レコードには日付形式でカレンダーの始点と終点を設定する。
image.png

テーブルの何処かを選択した状態で[データ]タブ-[取得と変換]-[テーブルから]を選択する。
image.png

Power Queryに取り込まれたので、ここからカレンダーに変換していく。
image.png

カレンダーで使うデータの準備

カレンダーを生成するには日付形式の方が都合が良いので、まずはデータ型を設定する。
列名の左側をクリックし、出てきたメニューから[日付]を選択。
image.png
image.png

両方の列にデータ型を設定したら次に進む。
image.png

カレンダーの生成

[適用したステップ]の一番下のステップを右クリックし、[後にステップの挿入]を選択。
image.png
[カスタム1]というステップが追加される。
image.png
数式バーに以下の数式を入力。

= List.Dates(
        変更された型[年月最小値]{0}
      , Number.From(変更された型[年月最大値]{0} - 変更された型[年月最小値]{0}) + 1
      , #duration(1, 0, 0, 0)
    )

数式バーはここ。
image.png

今回使うList.Dates関数によるカレンダー生成は、Power BIの神が以下の記事で詳しく解説しています。
Power Query の カスタム列の追加 を使わずに 日付テーブル を生成
公式リファレンスは此方。
List.Dates - PowerQuery M | Microsoft Docs

List.Dates関数で使った変更された型[年月最小値]{0}ステップ名[列名]{列のN番目の要素}という意味。
ここではステップ[変更された型]の[年月最小値]列の0番目を値を取ってくるという動作になる。
好きなステップからデータを取り出せるので、覚えておいて損はない。

テーブルに変換して完成

List.Dates関数はListを返すので、最後は[テーブルへの変換]を選んでテーブルにしたらフィニッシュ。
image.png

後はPower Query上で加工するなり、Excelデータモデルに読み込んでDAXで計算列を作るなり、好きに使いましょう。

日付でカレンダーのコード

自分用面倒な方向け。
テーブル名と列名さえ同じならこれを詳細エディターに張り付ければ終わりです。

日付でカレンダー
let
    ソース = Excel.CurrentWorkbook(){[Name="元テーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"年月最小値", type date}, {"年月最大値", type date}}),
    Dates = List.Dates(
        変更された型[年月最小値]{0}
      , Number.From(変更された型[年月最大値]{0} - 変更された型[年月最小値]{0}) + 1
      , #duration(1, 0, 0, 0)
    ),
    ConvertedToTable = Table.FromList(
        Dates
      , Splitter.SplitByNothing()
      , type table [日付 = date]
    )
in
    ConvertedToTable

年度でカレンダーを作る

日付入れるのが面倒な方向け。
image.png

データが年度だと日付型へのデータ加工が必要になる。
それ以外は[日付でカレンダーを作る]と全く同じ。

カレンダーで使うデータの準備

日付型へのデータ加工のため、データ型をテキスト型に変更する。
image.png

リボンの[列の追加]から[カスタム列]を選択。
image.png

[開始年度]Date.FromText関数を使い、日付形式に変換できるよう月日の部分を付け加える。
Date.FromText - PowerQuery M | Microsoft Docs
image.png

出来たカスタム列を日付形式に変換する。
image.png

同じ手順を[終了年度]にも行えば準備完了。
image.png

カレンダーの生成

ここから先は[日付でカレンダーを作る]と同じなので割愛。

年度でカレンダーのコード

例によってテーブルさえ用意すれば、詳細エディターに張り付けて終わりです。

年度でカレンダー
let
    ソース = Excel.CurrentWorkbook(){[Name="元テーブル"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"開始年度", type text}, {"終了年度", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "年月最小値", each Date.FromText([開始年度]&"0401")),
    変更された型1 = Table.TransformColumnTypes(追加されたカスタム,{{"年月最小値", type date}}),
    追加されたカスタム1 = Table.AddColumn(変更された型1, "年月最大値", each Date.FromText([終了年度]&"0331")),
    変更された型2 = Table.TransformColumnTypes(追加されたカスタム1,{{"年月最大値", type date}}),
    Dates = List.Dates(
        変更された型2[年月最小値]{0}
      , Number.From(変更された型2[年月最大値]{0} - 変更された型2[年月最小値]{0}) + 1
      , #duration(1, 0, 0, 0)
    ),
    ConvertedToTable = Table.FromList(
        Dates
      , Splitter.SplitByNothing()
      , type table [日付 = date]
    )
in
    ConvertedToTable

まとめ

  • Excelワークシート上からでもクエリを操作できる。
    • 一々データの更新が必要ですが、What-ifも出来ます。ExcelにはPower BIほど柔軟性は無いです…。
  • Power Queryでデータを加工する際にはデータ型に注意。
    • 関数は引数にデータ型が指定されている事があるので、リファレンスでよく確認すると予期せぬエラーを減らせます。
  • [後にステップの挿入]や[カスタム列]を使うと幅が広がる。
    • 慣れてくるとGUIでポチポチだけでは物足りなくなってくるので、積極的に使ってみてください。

あー仕事でExcelじゃなくて,Power Platformを触りたい(´・ω・`)
後、午前0時を回らないとやる気がでない現象をどうにかしたい。

Yellow11
Office365ユーザーで部署の SharePoint 管理者みたいなことをやっています。 #SharePoint #Office365 #PowerPlatform #PowerBI #PowerApps #PowerAutomate #MicrosoftFlow
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away