新しい元号 "令和" が発表となったわけですが、カレンダーテーブルに "平成31年/令和元年" みたいな見出し用の列を追加しておこうと思ったので。DAX でもできるのだけど、カレンダーテーブルは多くのレポートで共通で Power BI データフローで共有するとよいと思うのです。なので Power Query で用意しておくことも大事。
DAX で
日付列 [Date] から FORMAT 関数(DAX) で "ggge年" を指定しても、日付についての元号表記になるからちょっと手を加える。
ggge+ =
CONCATENATEX(
DISTINCT(
UNION(
ROW(
"ggge", FORMAT(DATE(YEAR([Date]), 1, 1), "ggge年")
),
ROW(
"ggge", FORMAT([Date], "ggge年")
)
)
),[ggge]
,"/"
)
"ggge年"の利用については、"モデルの言語"が ja-jp なときでよきところで使えばよいでしょう。新しい元号に対するサポートはそのうちされるかと。
そもそも、FORMAT 関数については、
FORMAT 関数(DAX) | Microsoft Docs
CAUTION
The format strings supported as an argument to the DAX FORMAT function are based on the format strings used by Visual Basic (OLE Automation), not on the format strings used by the .NET Framework. Therefore, you might get unexpected results or an error if the argument does not match any defined format strings. For example, “p” as an abbreviation for “Percent” is not supported. Strings that you provide as an argument to the FORMAT function that are not included in the list of predefined format strings are handled as part of a custom format string, or as a string literal.
ですから。
Power Query で
レポートを作るたびにPower BI Desktop でコピペしながらこをれ使うということではなく、Power BI データフロー で使うのです。
(_date as date) as record =>
let
EraData = [
_Year = Date.Year(_date),
_Era1 = Table.SelectRows(
Table.SelectRows(
Source,
each [EndDate] >= #date(_Year, 1, 1)
),
each [StartDate] <= Date.EndOfYear(_date)
),
_Era2 = Table.AddColumn(
_Era1,
"ggge",
each
let
_StartYear = Date.Year([StartDate])
in
[EraName]
& (
if
_Year = _StartYear
then
"元"
else
Text.From(_Year - _StartYear + 1)
)
& "年"
),
_Era3 = Table.Sort(
_Era2,
{"StartDate", Order.Ascending}
)[ggge],
_Era4 = Table.SelectRows(
_Era2,
each
[StartDate] <= _date
and [EndDate] >= _date
)[ggge],
EraDate = [
#"ggge+" =
if
List.IsEmpty(_Era3)
then
Text.From(_Year) & "年"
else
Text.Combine( _Era3, "/"),
gggeYMD = Date.ToText(_date, _Era4{0}? & "MM月dd日", "ja-jp")
]
][EraDate],
Source = #table(
type table [
StartDate = Date.Type,
EndDate = Date.Type,
EraName = Text.Type
],
{
{#date(1868, 1, 25), #date(1916, 7, 29), "明治"},
{#date(1912, 7, 30), #date(1926, 12, 24), "大正"},
{#date(1926, 12, 25), #date(1989, 1, 7), "昭和"},
{#date(1989, 1, 8), #date(2019, 4, 30), "平成"},
{#date(2019, 5, 1), #date(9999, 12, 31), "令和"}
}
)
in
EraData
Power BI データフローの Power Query Online には [カスタム関数の呼び出し] コマンドはないので、Table.AddColumn( カレンダーテーブル, each 関数名([日付列])) という感じで適用するステップを追加すればよい。
思ったこと🙄
カレンダーテーブルには気が遠くなるような行数を抱えることはないし、Power BI データフローで共有したとしても更新することはほぼ皆無なので、多少ムチャしても全く問題にならないよね。
Power Query 記述はこれに限らずなんだけど、ロジックをそのままコードに変換するんじゃなくて、決定表から結果を読み取るロジックをコードに変換すればよいと思うの。