カレンダーテーブルに "平成31年/令和元年" っていう見出し用の列を追加したい

4975 ワード
PowerBI DAX PowerQuery PowerBI テキストリンク

新しい元号 "令和" が発表となったわけですが、カレンダーテーブルに "平成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 記述はこれに限らずなんだけど、ロジックをそのままコードに変換するんじゃなくて、決定表から結果を読み取るロジックをコードに変換すればよいと思うの。

その他