Power BI の棒グラフでX軸ラベルのカスタマイズを行う

問合せ履歴などをリストで溜めていてPower BIでグラフを表示したいなと思ったときに、標準の積み上げ棒グラフだと少し不足があったのでX軸のラベルをカスタマイズしてみました。

元のデータは日付と問い合わせタイプが入っていて、1つの日付で複数レコード、また毎日問い合わせがあるわけではないので日付は歯抜けになっています。

元のデータと標準の棒グラフ

標準の棒グラフだと、月まで表示した時に何月かがわからないのが自分の課題でした。理想としては『月初1日だけ月を表示して、それ以外は日付のみ表示する』ようにしたい。そんなX軸ラベルのカスタマイズを考えます。

ラベル用のデータを作成する

最初に思いついたのが、ラベル用にカレンダーテーブル＋カスタム列で構成されるテーブルを用意して、日付でリレーションを作成すればいいのでは？？ということでした。

まず自作のカレンダーテーブル Power Queryを使って、以下のように生成します。

=Table.FromList(List.Dates(#date(2021, 1, 1), 30*2, #duration(1, 0, 0, 0)),Splitter.SplitByNothing(),null,null,ExtraValues.Error)

カレンダーテーブルの作成

List.Dates()で日付リストを作って、そこからテーブルに変換しています。さらに列を日付型に変換しています。

あとはカスタム列で、1日だったらMM/dd形式に、それ以外ではdd形式で日付を表示させます。

= Table.AddColumn(
    #"Changed Type", "Custom",
    each if Date.Day([Column1]) = 1 
         then Date.ToText([Column1],"MM/dd") 
         else Date.ToText([Column1],"dd")
)

月初1日だけ月を表示するカスタム列

あとはこのカレンダーテーブルの日付列 (Column1) と元のデータの日付列 (Created) の間にリレーションを作れば、綺麗に表示されるはず！...

X軸ラベルをカスタム列にした場合

...月初が最初にまとまっている ＆ 1月の日付と2月の日付で区別がなくなっている...

このままだとどのようにソートを変えてもうまく表示されません。困った。

カスタム列をソートするための列を設定

困っているところで Takeshi Kagataさん に助けていただきました。なるほど、テキスト型のカスタム列をソートする際に使う列を定義できるらしい。

列で並び替え / Sort by Column でラベルで使う列のソートを定義するのだ pic.twitter.com/FunAH5NMDF

— Takeshi Kagata / Power Platform 部 Power BI 科 🛏💤 (@PowerBIxyz) March 9, 2021

この操作はPower QueryではなくPower BIのデータ表示するタブで行います。

よーし見つけたと思って操作してみると...

列のソートを変更しようとするとエラーが。

エラーです。これはどうやらカスタム列側で重複があることに起因していそうです。なるほど、確かに1月21日と2月21日は重複していますね。これをを排除すればよさそう。

カスタム列の重複を排除する

1月21日と2月21日を区別するのであれば、月数に依存する加工をすればよさそうです。ナイーブにはスペースを月数分だけ後ろにつければいいかなと思いましたが、もう少し便利な魔法の合言葉 Character.FromNumber(8203) というものがありましたので、こちらを採用します。

でも考えかたは変わらず。

1月21日と2月21日を区別するには

結局、カスタム列追加部分のPower Queryでの式を以下のように変更しました。

= Table.AddColumn(
    #"Changed Type", "Custom",
    each if Date.Day([Column1]) = 1 
         then Date.ToText([Column1],"MM/dd") 
         else Date.ToText([Column1],"dd")&Text.Repeat(Character.FromNumber(8203),Date.Month([Column1]))
)

ポイントはText.Repeatでゼロ幅文字を月数分連結させていることです。

ゼロ幅文字を月数分追加した

あとは先ほど失敗した、カスタム列のソートを設定して、グラフに追加すると...

完成

無事完成しました！

これで、月の初日だけ月が表示されて、それ以外は日のみ表示されるようにできます！

最終的にX軸用のカスタム列を含むカレンダーテーブルを作成するクエリは以下のようになりました。

let
    Source = Table.FromList(List.Dates(#date(2021, 1, 1), 30*2, #duration(1, 0, 0, 0)),Splitter.SplitByNothing(),null,null,ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Added Conditional Column" = Table.AddColumn(
    #"Changed Type", "Custom",
    each if Date.Day([Column1]) = 1 
         then Date.ToText([Column1],"MM/dd") 
         else Date.ToText([Column1],"dd")&Text.Repeat(Character.FromNumber(8203),Date.Month([Column1]))
)
in
    #"Added Conditional Column"