こんばんは！Riekoです。
Prep極める挑戦状6をやってみた
Tableau Prepユーザー会及び、私が2023年から参画しているPreppinData勉強会から誕生した、Prep極める問題集
やりたいと思いつつもこれまで初中級を少しやったのにで止まっていました。
が、いきなり「挑戦状6：給与明細データをPDFから作成せよ！」というめっちゃむずいのに挑戦することに。
一足飛び過ぎじゃない？
なぜならPDFやら神Excelやら、実際のデータはこんなもんだという現実に気づき、
最近Prep筋、いやデータ加工筋がたるんでるのもあって戒めを込めて
「PDFの地獄みたいなデータを綺麗にしてみよう」
と思い挑戦してみました。
そしてPrepからの...
Prepでさくっと加工し、余裕があればPower Queryでもやってみようかな、
と気楽に考えていたのですが
心が折れました。
なぜならこの挑戦状の肝である通り、
PrepはPDFに対してSouce Row Numberが付きません。
つまり、もとの行の並び順を保証してくれません。
なんということでしょう。
出題者であるもりた大先生はこれを何とかするということを望んでおられたのでしょうが、
すみません、諦めました。
こころが折れるまではこのようなフローの残骸が出来上がっております。
こちらは比較的シンプルなのは、「もうやだーーー」と後続のフローを消しまくったからです。
40手前のおばさんが日曜に発狂して、情けないですね。
Power Queryに乗り換え
Power Queryなら行番号保持できる！インデックスいくらでもつけられる！
その上で、Power Queryの強みは複数フォルダから一括でとってくれることです。
というわけで、Power Queryに鞍替え。
その手順は
データを取得＞フォルダ
を選んであとはPDFが12か月分入っているフォルダを選択するだけ。
パラメータやらサンプルやら、
要はサンプルデータを加工すればあとは12ファイル全部適用してくれるっぽいですが、
なんせ全く同じレイアウトを保証することはできません。
（案の定、12月だけ「年末調整」データがありました）
なので全部くっついた奴からとにかく加工、加工していきました。
実務でだいぶ慣れたとはいえ、しんどいです。
もっとステップをシンプルにする方法はあると思いますが、
時には複製してくっつけたり
あまりに増えるの嫌すぎるからM言語でスッキリさせたり、
格闘すること約5時間。
貴重な日曜の半分を費やしました。
（これやるまで土日キャンプをエンジョイしたからよいのですが）
というわけで、できたぞ！あーしんど！
ここでは詳細なステップは解説しませんが、
要は
- Power Queryはどんなステップに行ってもPDFでも、元データの行番号をつけられる
- 同じフォルダから一括でユニオンするのもラクラク（これはPrepでもそう）
ということが本記事の肝です。
これまでPrepが好き過ぎてPower Query毛嫌いしていましたが、
Excelにもくっついているし、わからなければM言語でAIにある程度補わせることも可能ということで、底知れない可能性を感じました。
Sharepoint等M365製品との連携含め、
ちょっとだいぶ頑張りたいと思います！
内容の薄っぺらい記事ですみません。(*ﾉωﾉ)
一応Power Queryで加工完成後のM言語を掲載します。
が、これだけでなくCalenderクエリ作ったりそちらでも多少加工しているのでこれがすべてではないです。
（気が向いたらpbixファイルをGoogle Driveにおいてこちらにリンクを追記しますね。需要あればですが..）
let
ソース = Folder.Files("C:\Users\hazuy\OneDrive\ドキュメント\ダイハツ\Gr付提案\PrepPDF給与明細\data"),
#"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
#"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
フィルターされた行 = Table.SelectRows(展開されたテーブル列1, each true),
追加されたインデックス = Table.AddIndexColumn(フィルターされた行, "インデックス", 1, 1, Int64.Type),
#"名前が変更された列 " = Table.RenameColumns(追加されたインデックス,{{"インデックス", "全体index"}}),
挿入されたテキスト範囲 = Table.AddColumn(#"名前が変更された列 ", "テキスト範囲", each Text.Middle([Source.Name], 7, 4), type text),
#"名前が変更された列 2" = Table.RenameColumns(挿入されたテキスト範囲,{{"テキスト範囲", "Year"}}),
挿入されたテキスト範囲1 = Table.AddColumn(#"名前が変更された列 2", "テキスト範囲", each Text.Middle([Source.Name], 11, 2), type text),
#"名前が変更された列 3" = Table.RenameColumns(挿入されたテキスト範囲1,{{"テキスト範囲", "Month"}}),
フィルターされた行1 = Table.SelectRows(#"名前が変更された列 3", each true),
下方向へコピー済み = Table.FillDown(フィルターされた行1,{"Column1"}),
// 21ごとに 4行だけ残す（2,3 / 23,24 ...）
master =
Table.SelectRows(
下方向へコピー済み,
each let r = Number.Mod([全体index] - 2, 21) in r = 0 or r = 1
),
削除された他の列2 = Table.SelectColumns(master,{"Column2", "Column3", "Column4", "Column5"}),
soshiki = Table.SelectColumns(削除された他の列2, {"Column2", "Column3"}),
ピボットされた列 = Table.Pivot(soshiki, List.Distinct(soshiki[Column2]), "Column2", "Column3", List.Min),
追加されたインデックス1 = Table.AddIndexColumn(ピボットされた列, "インデックス", 1, 1, Int64.Type),
syain = Table.SelectColumns(削除された他の列2, {"Column4", "Column5"}),
ピボットされた列1 = Table.Pivot(syain, List.Distinct(syain[Column4]), "Column4", "Column5", List.Min),
追加されたインデックス2 = Table.AddIndexColumn(ピボットされた列1, "インデックス", 1, 1, Int64.Type),
// 21ごとに 4行だけ残す（2,3 / 23,24 ...）
kintai1 =
Table.SelectRows(
下方向へコピー済み,
each let r = Number.Mod([全体index] - 4, 21) in r = 0 or r = 1
),
削除された他の列3 = Table.SelectColumns(kintai1,{"Year", "Month", "Column7", "Column5", "Column4", "Column3", "Column2", "Column1"}),
昇格されたヘッダー数 = Table.PromoteHeaders(削除された他の列3, [PromoteAllScalars=true]),
フィルターされた行2 = Table.SelectRows(昇格されたヘッダー数, each ([有休残日数] <> "有休残日数")),
ピボット解除された列 = Table.UnpivotOtherColumns(フィルターされた行2, {"2024", "01", "勤怠"}, "属性", "値"),
#"名前が変更された列 4" = Table.RenameColumns(ピボット解除された列,{{"属性", "項目"}, {"値", "数値"}, {"勤怠", "区分"}}),
kintai2 =
Table.SelectRows(
下方向へコピー済み,
each let r = Number.Mod([全体index] - 6, 21) in r = 0 or r = 1
),
昇格されたヘッダー数1 = Table.PromoteHeaders(kintai2, [PromoteAllScalars=true]),
フィルターされた行3 = Table.SelectRows(昇格されたヘッダー数1, each ([普通残業時間] <> "普通残業時間")),
削除された列 = Table.RemoveColumns(フィルターされた行3,{"", "6"}),
ピボット解除された列1 = Table.UnpivotOtherColumns(削除された列, {"salaly_202401.pdf", "勤怠", "2024", "01"}, "属性", "値"),
#"名前が変更された列 5" = Table.RenameColumns(ピボット解除された列1,{{"属性", "項目"}, {"値", "数値"}}),
削除された列1 = Table.RemoveColumns(#"名前が変更された列 5",{"salaly_202401.pdf"}),
#"名前が変更された列 13" = Table.RenameColumns(削除された列1,{{"勤怠", "区分"}}),
shikyu1 =
Table.SelectRows(
下方向へコピー済み,
each let r = Number.Mod([全体index] - 8, 21) in r = 0 or r = 1
),
削除された他の列5 = Table.SelectColumns(shikyu1,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Year", "Month"}),
昇格されたヘッダー数2 = Table.PromoteHeaders(削除された他の列5, [PromoteAllScalars=true]),
フィルターされた行4 = Table.SelectRows(昇格されたヘッダー数2, each ([役職手当] <> "役職手当")),
ピボット解除された列2 = Table.UnpivotOtherColumns(フィルターされた行4, {"支給", "2024", "01"}, "属性", "値"),
#"名前が変更された列 6" = Table.RenameColumns(ピボット解除された列2,{{"属性", "項目"}, {"値", "数値"}, {"支給", "区分"}}),
shikyu2 =
Table.SelectRows(
下方向へコピー済み,
each let r = Number.Mod([全体index] - 10, 21) in r = 0 or r = 1
),
削除された他の列6 = Table.SelectColumns(shikyu2,{"Column1", "Column2", "Column3", "Column5", "Column7", "Year", "Month"}),
昇格されたヘッダー数3 = Table.PromoteHeaders(削除された他の列6, [PromoteAllScalars=true]),
フィルターされた行5 = Table.SelectRows(昇格されたヘッダー数3, each ([休日出勤手当] = "0")),
ピボット解除された列3 = Table.UnpivotOtherColumns(フィルターされた行5, {"支給", "2024", "01"}, "属性", "値"),
#"名前が変更された列 7" = Table.RenameColumns(ピボット解除された列3,{{"属性", "項目"}, {"値", "数値"}, {"支給", "区分"}}),
koujo1 =
Table.SelectRows(
下方向へコピー済み,
each let r = Number.Mod([全体index] - 14, 21) in r = 0 or r = 1
),
削除された他の列7 = Table.SelectColumns(koujo1,{"Column1", "Column2", "Column3", "Column4", "Column5", "Year", "Month"}),
昇格されたヘッダー数4 = Table.PromoteHeaders(削除された他の列7, [PromoteAllScalars=true]),
フィルターされた行6 = Table.SelectRows(昇格されたヘッダー数4, each ([厚生年金保険料] = "33,855")),
ピボット解除された列4 = Table.UnpivotOtherColumns(フィルターされた行6, {"控除", "2024", "01"}, "属性", "値"),
#"名前が変更された列 8" = Table.RenameColumns(ピボット解除された列4,{{"属性", "項目"}, {"値", "数値"}, {"控除", "区分"}}),
koujo2 =
Table.SelectRows(
下方向へコピー済み,
each let r = Number.Mod([全体index] - 16, 21) in r = 0 or r = 1
),
削除された他の列8 = Table.SelectColumns(koujo2,{"Column1", "Column2", "Column3", "Column7", "Column6", "Year", "Month"}),
昇格されたヘッダー数5 = Table.PromoteHeaders(削除された他の列8, [PromoteAllScalars=true]),
フィルターされた行7 = Table.SelectRows(昇格されたヘッダー数5, each ([その他控除] = "0")),
#"名前が変更された列 9" = Table.RenameColumns(フィルターされた行7,{{"", "年末調整"}}),
ピボット解除された列5 = Table.UnpivotOtherColumns(#"名前が変更された列 9", {"控除", "2024", "01"}, "属性", "値"),
#"名前が変更された列 10" = Table.RenameColumns(ピボット解除された列5,{{"属性", "項目"}, {"値", "数値"}}),
フィルターされた行9 = Table.SelectRows(#"名前が変更された列 10", each ([数値] <> "")),
#"名前が変更された列 12" = Table.RenameColumns(フィルターされた行9,{{"控除", "区分"}}),
koujo3 =
Table.SelectRows(
下方向へコピー済み,
each let r = Number.Mod([全体index] - 18, 21) in r = 0 or r = 1
),
削除された他の列9 = Table.SelectColumns(koujo3,{"Column1", "Column2", "Column3", "Column7", "Year", "Month"}),
昇格されたヘッダー数6 = Table.PromoteHeaders(削除された他の列9, [PromoteAllScalars=true]),
フィルターされた行8 = Table.SelectRows(昇格されたヘッダー数6, each ([控除額合計] <> "控除額合計")),
ピボット解除された列6 = Table.UnpivotOtherColumns(フィルターされた行8, {"控除", "2024", "01"}, "属性", "値"),
#"名前が変更された列 11" = Table.RenameColumns(ピボット解除された列6,{{"属性", "項目"}, {"値", "数値"}, {"控除", "区分"}}),
// データ部分をユニオン
MeisaiUnion = Table.Combine({#"名前が変更された列 11",#"名前が変更された列 12", #"名前が変更された列 8", #"名前が変更された列 7", #"名前が変更された列 6",#"名前が変更された列 13",#"名前が変更された列 4"}),
#"名前が変更された列 14" = Table.RenameColumns(MeisaiUnion,{{"2024", "Year"}, {"01", "Month"}}),
追加されたカスタム = Table.AddColumn(#"名前が変更された列 14", "No", each 1),
MasterMerg =
Table.NestedJoin(
追加されたインデックス1, {"インデックス"},
追加されたインデックス2, {"インデックス"},
"R",
JoinKind.Inner
),
#"展開された R" = Table.ExpandTableColumn(MasterMerg, "R", {"社員コード", "社員名"}, {"社員コード", "社員名"}),
AllMerg =
Table.NestedJoin(
#"展開された R", {"インデックス"},
追加されたカスタム, {"No"},
"R",
JoinKind.Inner
),
#"展開された R1" = Table.ExpandTableColumn(AllMerg, "R", {"区分", "Year", "Month", "項目", "数値"}, {"区分", "Year", "Month", "項目", "数値"}),
変更された型 = Table.TransformColumnTypes(#"展開された R1",{{"Year", Int64.Type}, {"Month", Int64.Type}}),
削除された列2 = Table.RemoveColumns(変更された型,{"インデックス"}),
マージされたクエリ数 = Table.NestedJoin(削除された列2, {"Year", "Month"}, Calender_MonthEnd, {"Year", "MonthNo"}, "Calender_MonthEnd", JoinKind.LeftOuter),
#"展開された Calender_MonthEnd" = Table.ExpandTableColumn(マージされたクエリ数, "Calender_MonthEnd", {"Modified MonthEnd"}, {"Modified MonthEnd"}),
追加されたカスタム1 = Table.AddColumn(#"展開された Calender_MonthEnd", "支給日", each try #date(Number.From([Year]), Number.From([Month]), Number.From([Modified MonthEnd])) otherwise null),
変更された型1 = Table.TransformColumnTypes(追加されたカスタム1,{{"支給日", type date}}),
削除された列3 = Table.RemoveColumns(変更された型1,{"Year", "Month", "Modified MonthEnd"}),
並べ替えられた行 = Table.Sort(削除された列3,{{"支給日", Order.Ascending}}),
削除された重複 = Table.Distinct(並べ替えられた行, {"項目", "支給日"}),
フィルターされた行10 = Table.SelectRows(削除された重複, each ([数値] <> "0") and ([項目] <> "控除額合計"))
in
フィルターされた行10
おわりに
データを加工することは、データを理解すること。
可視化やダッシュボードも大事だけど、
まずはデータを見て、理解し、綺麗な形に整える。
その大事さを色んな方に広めたいと思っています✨
敬愛するTableau Prepユーザー会のMissionも、おなじですね💕
Rieko