More than 1 year has passed since last update.

履歴テーブルを使った、情報の更新

Last updated at 2024-01-07Posted at 2024-01-07

※この記事は、試したらなんかうまくいったというレベルなのでおかしな点があったら、指摘していただけると非常にありがたいです。

背景

外国人労働者が結構いる工場
管理者は外国人社員の「現在の」日本語レベルを一覧で見れるようにしたい。
- 仕事の配置などの参考にする
手元にある情報
- 採用時の日本語レベルの情報
- 日本語検定合格時に受けた報告の記録

記録されている情報

採用時に確認
- 採用前の段階で、日本語検定のレベルを確認してその情報を記録している。
  - 記録している情報は以下のとおり
    - 従業員番号
    - 名前
    - 日本語レベル
    - 採用日
日本語レベル確認履歴
- 日本語検定をとった報告を受けたら、合格したレベルと取得日時をを記録として残している。
  - 記録している情報以下のとおり
    - 記録ID
    - 従業員番号
    - 取得した日本語検定のレベル
    - 取得日

PowerQueryを使った実際の操作

まずはデータの整理

採用時の情報は以下のようなテーブル名にしてカラムは以下のようにする。
- テーブル名：EmployeeMasterTable
  - Employee Number
  - Name
  - Japanese Level
  - Hire Date
日本語レベルの確認履歴は以下のようなテーブル名にしてカラムは以下のようにする。
- テーブル名： JapaneseLevelHistory
  - ID
  - Employee Number
  - Japanese Level
  - Check Date

データを取り込んで操作

PowerQueryの細かい話はここでは省く。操作の大まかな流れとしては
1.「従業員情報」と「日本語レベルの更新履歴」のデータを取得
2.「日本語レベルの更新履歴」から各社員の最新情報のみ抽出したテーブル取得
3.2で取得したテーブルを使って、各社員の最新の日本語レベル一覧表を作る。

「更新履歴」から「各社員の最新情報」のみ抽出

Employee Numberを選択し、タブ「変換」->「グループ化」をクリック
新しい列名を「最新の情報」、操作を「最大」、列を「Check Date」にする
- 従業員番号の中で、最新の日付(最も大きい値)のデータを抽出する

タブ「ホーム」->タブ「クエリ」 -> 「詳細エディタ」をクリック

 この時点で詳細エディタを見ると以下のようなコードが出来ている。
 let
     ソース = Csv.Document(File.Contents("--"),省略),
     昇格されたヘッダー数 = Table.Promote.....
     変更された型 = Table.Trans.....,
     グループ化された行 = Table.Group(変更された型, {"Employee Number"}, {{"最新の情報", each List.Max([Check Date]), type nullable date}})
     .........................

「グループ化された行」の末尾に「,」をつけてその下に以下のコードを入力
```
  結合 = Table.Join(変更された型,{"Employee Number","Check Date"},グループ化された行,{"Employee Number","最新の情報"})
```
やっていることは、PowerQueryの操作の過程でできた「変更された型」というテーブルに、「グループ化された行」というテーブルを二つのキーを使って結合している。

上記で求めた「履歴情報」から作った「各社員の最新情報」を利用して「社員情報」を更新

「社員情報」：EmployeeJapaneseLevelを基本に、「各社員の情報」を結合する
- キーになるカラムは両方とも「Employee Number」
- 結合の種類は「左側結合」
テーブルの展開時に「JapaneseLevel」「Check Date」のみを選択
更新日の追加：タブ「列の追加」->「カスタム列」を選択
- 新しい列名を「Last Check Date」とする
- カスタム列の式を以下のように入力
```
  if [JapaneseLevelHistory.Check Date] = null then [Hire Date] else [JapaneseLevelHistory.Check Date]
```
最新の日本語レベルを追加：タブ「列の追加」->「カスタム列」を選択
- 新しい列名を「Last Check Date」とする
- カスタム列の式を以下のように入力
```
  if [JapaneseLevelHistory.Japanese Level] = null then [Japanese Level] else [JapaneseLevelHistory.Japanese Level]
```
あとは必要な列のみを残して、完了

今回の操作で分かったこと

PowerQueryの操作の途中でできたテーブルは後の操作で再利用できる
左側結合や右側結合と結合がいくつもあり、それを利用して色々できること。

以上です。

おまけ-利用したダミーデータ-

chatGPTで作成、従業員情報を以下のようなプロンプトを投げて作成

以下のテーブルのダミーデータを30件作ってください

テーブルの特徴
・カラムは、従業員番号、名前、日本語レベル、採用日
・従業員番号のデータ型は整数型
・名前のデータ型は文字列型で、アジア系の名前
・日本語レベルのデータ型は整数型で1〜3の整数
・採用日のデータ型で日付型、2021/4/3〜2021/8/12までの日付のどこか

実際にできたデータを、多少加工してできたのが以下。

EmployeeMasterTable

Employee Number,Name,Japanese Level,Hire Date
1,Min Zhang,3,2021/5/3
2,Jin Li,1,2021/7/16
3,Aiko Lin,2,2021/7/18
4,Li Wang,1,2021/8/7
5,Aiko Gupta,2,2021/4/9
6,Yuki Zhang,2,2021/5/14
7,Daichi Lin,2,2021/6/11
8,Hiroshi Kim,3,2021/6/1
9,Li Kim,1,2021/6/21
10,Yuki Wang,1,2021/7/5
11,Daichi Kim,2,2021/6/19
12,Daichi Chen,1,2021/6/19
13,Li Gupta,3,2021/4/19
14,Haruto Zhang,1,2021/7/1
15,Aiko Gupta,2,2021/4/16
16,Mei Zhang,1,2021/8/10
17,Yuki Huang,1,2021/5/17
18,Mei Chen,2,2021/4/18
19,Mei Nguyen,3,2021/4/6
20,Daichi Kim,1,2021/5/6
21,Haruto Li,2,2021/4/15
22,Daichi Wang,2,2021/4/17
23,Daichi Huang,1,2021/7/30
24,Li Kim,3,2021/4/8
25,Hiroshi Huang,2,2021/4/24
26,Mei Kim,2,2021/5/13
27,Haruto Lin,3,2021/5/27
28,Haruto Kim,1,2021/5/4
29,Jin Chen,1,2021/6/15
30,Jin Gupta,1,2021/5/19

JapaneseLevelHistory

ID,Employee Number,Japanese Level,Check Date
1,3,3,2022/9/13
2,11,3,2022/9/14
3,14,3,2022/9/27
4,25,4,2022/10/7
5,23,3,2022/10/14
6,26,4,2022/10/23
7,18,4,2022/11/6
8,15,3,2022/11/12
9,24,5,2022/11/18
10,1,5,2022/11/28
11,22,4,2022/12/2
12,28,3,2022/12/10
13,15,4,2022/12/23
14,11,4,2022/12/24
15,3,4,2022/12/28
16,15,5,2023/1/8

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up