LuckyTemplates の日付間の労働時間ソリューション

今日のブログでは、 LuckyTemplates を使用して日付間の合計労働時間を計算する、 Access Analytic のStart Stop Challengeへのアプローチを紹介します。このチュートリアルのビデオ全体は、このブログの下部でご覧いただけます。

このタスクは、各月および日付のスタッフ メンバーの合計時間数を計算することです。開始日と終了日は月をまたぐ可能性があり、タイプミスの可能性を考慮してステータスを考慮しました。 

目次

• LuckyTemplates のデータセットの日付間の労働時間
• 行のフィルタリング
• カスタム列の追加
• LuckyTemplates の日付間の労働時間: 変数
• LuckyTemplates の日付間の労働時間: 予測
• LuckyTemplates の日付間の労働時間: 集計値
• 結論

LuckyTemplates のデータセットの日付間の労働時間

Power Query エディターに移動しましょう。 

このチャレンジのデータは次のとおりです。スタッフ名、開始日、開始時刻、終了日、終了時刻、ステータスの列を含むテーブルがあります。

行のフィルタリング

まずはステータスに基づいて行を選択しましょう。クエリ エディターはこのロジックを支援します。「ステータス」の横にあるドロップダウン矢印をクリックします。[テキスト フィルター]を選択し、[次で始まらない] を選択します。 

表示される[行のフィルター]ウィンドウに「e」と入力します。次に、「OK」をクリックします。

まだ何も起こりませんが、データ内に大文字の「E」で始まる小文字の「e」と「Exclude」を書き込むので問題ありません。

これを修正するには、 Text.StartsWithのオプションの 3 番目のパラメーターを使用します。数式バーで、Text.StartsWith 関数の末尾に移動します。カンマを追加して、Comparer.OrdinalIgnoreCase を入力しましょう。チェックマークをクリックすると、除外行が削除されます。

もちろん、最初の「E」を付けずに「Exclude」と誤って入力した人がいる可能性もあります。その場合、状況に応じて追加のロジックを作成できます。 

関数を拡張しText.Contains を使用することで、単語に「X」が含まれていないかどうかをテストできます。 これを行うには、または Text.Contains ([Status], “x”, Comparer.OrdinalIgnoreCaseを追加します。

最初の句と比較して、「e」を「x」に置き換えていることに注目してください。大文字と小文字は無視し続け、両方の句を括弧で囲みます。チェック マークをクリックすると、 ExcludeまたはXcludeステータスを含む行を含まないテーブルが表示されます。

カスタム列の追加

それでは、このチャレンジの主要部分に進みましょう。UI を使用してカスタム列を追加し、エディターに切り替えます。 

テーブルの左上隅にあるミニテーブルアイコンから始めます。それをクリックし、ドロップダウンで[カスタム列の追加]を選択します。 

現時点では、後で必要になるデータを取り込むことだけに関心があります。複数のフィールドを操作するので、角括弧で表されたレコード初期化子を使用してレコードを作成しましょう。 

変数を作成し、使用可能な列を割り当てましょう。まずは「SD」と入力します。これは開始日となります。これを行うには、右側の列の[開始日]をクリックします。

変数ST、ED、 ETに対しても同じことを行い、それぞれStart Time、Stop Date、 Stop Timeに割り当てます。 次に、閉じ括弧でレコードを閉じます。「OK」をクリックします。

レコードを含むカスタム列がテーブルに追加されます。「記録」の横の空白をクリックすると、プレビュー ペインが開きます。これにより、この行からすべての値が取り込まれます。

LuckyTemplates の日付間の労働時間: 変数

次に、 [詳細エディター]ウィンドウを使用して変数をフォーマットできます。Advanced Editor ウィンドウは次のようになります。 

次に、変数を別々の新しい行に入れてみましょう。

これで、必要なロジックを拡張できます。したがって、ここではレコードの代わりに、ネストされた let 式を使用することもできます。値が割り当てられた複数の変数またはフィールド名があるため、これらのどちらも機能することに注意してください。

現在、開始日はテキスト値として書き込まれているため、これを適切な日付形式に変換する必要があります。これを行うには、関数Date.FromTextをラップします。 

開始時間も文字列としてフォーマットされているため、数値に変換する必要があります。次に、それを 100 で割って四捨五入して小数点以下を削除します。 

Number.From関数を使用して、開始日の値を100で除算します。次に、それらを次の関数Number.Round内にラップし、最後に , 0を追加して、小数点以下を切り捨てます。

[終了日]と[終了時刻]については、それぞれ[開始日]と[開始時刻]で使用したのと同じ関数をコピーし、適切な変数に置き換えます。

変数が適切にフォーマットされたら、追加ロジック用にレコードに別のフィールドを作成します。Enterを押して新しい行を作成します。 

最初の日から終了日までの日付のリストを作成する必要があります。これらの日付をLDと呼び、関数List.Datesを使用します。 

この関数の最初のパラメータは、 SDとなる開始日です。次に、数値としてのカウント 、または開始日と終了日の差が必要です。これは、 Number.From関数に続いて ( ED – SD) + 1 を使用して取得できます。次のスクリーンショットのセットには「+1」が表示されていませんが、 +1があるはずであることに注意してください。

3 番目のパラメーターは期間としてのステップと呼ばれ、1 日の増分が必要です。これは、Duration.From (1) を使用して取得できます。 

最初の開始日から終了日までの日付のリストを作成しました。次にしたいのは、それに伴う時間のリストを作成することです。 

これをリストタイムのLTと呼びましょう。LT では 1 日の期間を設定できますが、これには複数日にまたがる場合とは若干異なるロジックが必要です。したがって、そのための条件を作成する必要があります。 

「開始日が終了日と同じ場合は、終了時刻から開始時刻を引いたもの」という条件が必要です。結果はリスト形式である必要があるため、{ }で表されるリスト初期化子を使用します。 

ここで、日付範囲が複数日にまたがる場合、最初の日付は 24 から開始時刻を引いたものになります。これをelseステートメントとして条件に追加します。 もう一度、中括弧を使用してリストとしてフォーマットします。

この時点では、他の丸一日は考慮されていないため、アンパサンド記号 (&) を使用して複数のリストを追加します。丸一日ごとに、1 日の 24 時間を表す値 24 を含むリストを作成します。これにはList.Repeatを使用します。 

List.Repeat関数を使用して、24 を含むリストを作成し、 LDの日数を数えてそれを何度も繰り返します。これを実現するには、List.Count (LD)を使用し  てから 2 を減算します。これは、開始日用に別のリストがあり、終了時間用に別のリストを作成するためです。 

つまり、これにより、1 日あたり 24 時間だけのリストが作成されます。 

終了時間については、アンパサンドを使用してリストとして再度追加できます。次に、リスト初期化子を使用して、ET を呼び出しましょう。 

これまでのところ、長さが互いに等しい 2 つの大きなリスト (日付のリストと時刻のリスト) ができました。これら 2 つのリストから 1 つのテーブルを構築できます。 

新しい行で、テーブルに別の変数を作成します。これをtと呼び、関数Table.FromColumnsを使用します。この関数にはリストとしてリストが必要なので、LD を使用します。 

当社のLD には、開始日から終了日までの個々の日付が含まれています。LDを渡してDate.EndOfMonth関数を呼び出すことで、日付のリストを月末の日付値に変換できます。次の行では、LTも取得しましょう。 

Enterを押して別の行を作成し、テーブルをtype tableとして設定します。テーブルには 2 つの列があり、最初の列は日付列になります。したがって、Date列を作成し、Date.Type関数を呼び出します。

2 番目の列は時間列であり、整数になります。したがって、 Hrs列を作成し、Int8.Type を呼び出します。

レコードを閉じるための閉じ括弧と、Table.AddColumn関数を閉じるための閉じ括弧があります。「完了」をクリックすると、カスタム列の下にレコードのリストが表示されます。 

もう一度、各レコードの横の空白をクリックすると、画面の下部にテーブルが開きます。 

最後の行にt テーブルがあることに注意してください。レコードの 1 つにドリルダウンしてみましょう。最初の結果を右クリックし、[新しいクエリとして追加] を選択します。 

前と同じ概要が表示され、「表」をクリックすると、日付と時間の値が表示されます。

ただし、私たちが主に関心があるのは最終出力テーブルです。これに重点を置くために、 [カスタム]を右クリックし、 [削除]を選択してヘルプ クエリを削除しましょう。ポップアップ ウィンドウで[削除]をクリックして確認します。

削除したら、最初のクエリに戻り、もう一度「レコード」の横の空白をクリックします。先ほどと同じ結果が表示されます。ただし、今回は、数式バーを展開して、右括弧の横にある[t]を呼び出してみましょう。

[Custom] 列では、各Record がTableに変更されていることがわかります。 

LuckyTemplates の日付間の労働時間: 予測

私たちが見ているテーブルには、スタッフ名とカスタム列という 2 つの列しかありません。射影を使用して、このテーブルからこれら 2 つの列だけを保持できます。 

数式バーに移動し、右括弧の横にある角括弧内にフィールドを配置して、保持するフィールドを選択します。次にチェックマークを押します。

この結果が得られるはずです。

Custom の横にある矢印アイコンをクリックすると、ネストされたテーブルを展開できます。[元の列名をプレフィックスとして使用する] チェックボックスをオフにして、[OK] をクリックします。

テーブルは次のように表示されるはずです。

LuckyTemplates の日付間の労働時間: 集計値

あとはこれらの値を集計するだけです。これを行うには、「スタッフ名」列を選択し、「変換」に進みます。 

ポップアップ ウィンドウで、集計したい値である[値] 列の下の[時間]を選択します。次に、[集計値関数]で[合計]を選択します。次に「OK」をクリックします。

最終的な出力は次のようになります。 

LuckyTemplates で日付テーブルを作成するLuckyTemplates の非標準日付テーブルの可能な最速の時間比較
DateTime 値: 秒を取り除く方法

結論

このブログでは、Access Analytic の Start Stop Challenge に対する 1 つのアプローチについて説明しました。各スタッフ メンバーの 1 か月の総労働時間を計算する方法と、データ内のタイプミスの可能性への対処方法を学習しました。 

この手法と LuckyTemplates を使用すると、日付間の労働時間を自分の組織内で簡単に計算したり、LuckyTemplates の知識とスキルを深めるための練習として簡単に計算したりできます。