[Power BI] 数値型やテキスト型で記述された日付列を使用して増分更新を設定しよう！🎈

はじめに

Power BIデータセット／データフローで増分更新を設定する際に、DateTime型の列が必要なのですが、Int型で日付列をもっている場合も多くあり困っていたので調べてみました。

なぜ困っている？

Int型で持っているデータを、DateTime型に型変換をすると、クエリフォールディングが効かなくなり、増分更新を設定することができません。パフォーマンスが悪くなります。なんとかクエリフォールディングを維持したまま変換できないものか！

クエリフォールディング？

クエリ フォールディング とは、ソース データを取得して変換するための単一のクエリ ステートメントを Power Query のクエリで生成する機能です。 Power Query のマッシュアップ エンジンでは、効率を高めるために、可能な場合は常にクエリ フォールディングを適用しようとします。

可能な場合はデータソース側で事前処理してもらえるクエリを投げて、PQ側では処理済みのデータをもらう仕組みのことです。クエリ言語に基づくデータソースが対応しているという記述があり、SQLServerやSharePointなど多くのデータソースが対応しています。逆にフラットファイル（ExcelやCSV）は対応していません。

参考ドキュメント

増分更新のメリット

行数の多いデータを毎回完全更新する必要がなくなり、データソースへの負荷が減り、更新のパフォーマンスもよくなります。増分更新を設定する場合にはクエリフォールディングが維持されていることが条件になります。

• 頻繁に変更されるデータの更新サイクルが少なくなる– DirectQuery モードを使用すると、高い更新頻度を必要とせずにクエリが処理されるため、最新のデータ更新内容が取得されます。
• 更新が高速化される - 更新する必要があるのは変更された最新のデータのみです。
• 更新の信頼性が高くなる - 揮発性のデータ ソースへの長時間の接続は必要ありません。 ソース データに対するクエリはより高速に実行され、ネットワークの問題が妨げになる可能性を減らします。
• リソースの使用が減る - 更新するデータが少ないと、Power BI とデータ ソース システムの両方のメモリや他のリソースの全体的な使用が減ります。
• 大規模なデータセットを有効にする - 何十億もの行になる可能性のあるデータセットは、更新操作ごとにデータセット全体を完全に更新しなくても拡張できます。
• 簡単なセットアップ -増分更新のポリシー は、ほんの数タスクで Power BI Desktopで定義されます。 発行されると、サービスでこれらのポリシーが更新ごとに自動的に適用されます。

Power Queryの処理によって、クエリフォールディングができなくなる

一般的に、次のリストに示す変換でクエリ フォールディングが阻止されます。 このリストはすべてを網羅するものではありません。

• 異なるソースに基づいたクエリの結合。
• 異なるソースに基づいたクエリのアペンド (結合)。
• 複雑なロジックによるカスタム列の追加。 複雑なロジックとは、データ ソースに同等の関数を持たない M 関数の使用を意味します。 たとえば、次の式では、OrderDate 列の値を書式設定します (テキスト値を返します)。
• 列のデータ型の変更。

今回の場合、問題は4番目の「列のデータ型の変更」ですね。Int型をDatetime型に変換すると、データソース側にクエリフォールディングをしてくれなくなります。クエリフォールディングができないと、増分更新の設定もできず、PowerQueryでの処理も多くなり、更新時間が長くなってしまいます。

方針

テーブル内のInt型日付列はいじらずに、増分更新を設定する際に必要になるパラメーター、

• RangeStart
• RangeEnd

をDateTime型からInt型に変換してテーブルと照合させます。

逆転の発想😆

パラメーターの設定

増分更新を設定するにはパラメーターを設定します。Power Query Editorから図のように操作します。

• RangeStart
• RangeEnd

は予約語で、名前を変えてはいけません。

カスタム関数の作成

DateTime型をInt型に変換をするため、下記のカスタム関数を記述します。これを適用すると、20220222のような8桁の整数がでてきます。

データを取得 → 空のクエリをクリックして貼り付けます。

数値型の日付列を持っている場合はこういう感じで、

= (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)

テキスト型の日付列な場合はこういう感じでOKかなと。

= (x as datetime) =>  Number.ToText(Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x))

Int型の日付列に適用

カスタム関数を使用して、フィルターをかけます。関数の引数にパラメーター（RangeStart,RangeEnd）をいれると図のようにフィルターがうまくかかりました。Datetime型のRangeStart,RangeEndをInt型に変換をしたうえで**[Date]**列をフィルターすることができています。

Filtered_Rows　= Table.SelectRows(
    test_data1,
    each
        [Date]　> Change_Date_to_Int(RangeStart)
        and [Date]　<= Change_Date_to_Int(RangeEnd)
　　)

あとは増分更新の設定をするだけ

まとめ

数値型の日付列をどうやって処理しようかなといろいろ調べていたら、ドキュメントにそのままそっくり載っていたので、結果的にほぼ写経して解決しました🎉
増分更新をしないといけないケースはとても多いと思うので、こういうテクニックもサクッと使えるようになりたいですね😎