Power Queryで神カレンダーから日付を導く(UI編)

ちょっと前にネタにしたD-Threeさんの下記の記事の内容をUIで何とかしようという記事です

神Excelの神カレンダー(?)から日付を導く
https://qiita.com/D-Three/items/ed9814b4b51f2bf9adcf

　

行外にあるデータを持ってくる方法(Power Query初心者向け：仮)
https://qiita.com/olt_yt/items/87d1cb9d041685a3c65a

この私的にデータの横抜きとか呼んでる方法の応用した処理です

全体処理

各ステップの作業を別列で残すように修正
「重複された列」と「 #"名前が変更された列 "」は説明用のステップで
実際には必要ありません

let
    //元データJSON
    X0 = "7ZY7DsIwEAXv4joS6+df4CqYMkdIhbg7LlIg4mIKChylsOREI6eY7Mj3p6trWkqsay42u1t7DGbmprbJaUntjffuNRFOkAuQi5BLkMuQK5CbIXftcTJZvVhsy3/SMnaqoBVBK4JWBK0IWhG0ImhF0Iq6VvZcgD6oDvhVhkEXUAU0AUVAD1CDpxr+KlbbkKfvIR8zWZ2/+YzVkLEKdErOqP0ualsM8i4GQ6btKPewftQebw==",

    //テーブル変換部
    X1 = Binary.FromText(X0),
    X2 =Binary.Decompress(X1,Compression.Deflate),
    X3 =Table.FromRecords(Json.Document(X2,932)),
    //テキスト化のために居れたスペース削除でNULL化
    置き換えられた値 = Table.ReplaceValue(X3,"　",null,Replacer.ReplaceValue,{"年月"}),

    //処理部
    追加された条件列 = Table.AddColumn(置き換えられた値, "1", each if [日] = "1" then "〇" else [年月]),
    重複された列 = Table.DuplicateColumn(追加された条件列, "1", "1 - コピー"),
    下方向へコピー済み = Table.FillDown(重複された列,{"1 - コピー"}),
    重複された列1 = Table.DuplicateColumn(下方向へコピー済み, "1 - コピー", "1 - コピー - コピー"),
    上方向へコピー済み = Table.FillUp(重複された列1,{"1 - コピー - コピー"}),
    重複された列2 = Table.DuplicateColumn(上方向へコピー済み, "1 - コピー - コピー", "1 - コピー - コピー - コピー"),
    置き換えられた値1 = Table.ReplaceValue(重複された列2,"〇",null,Replacer.ReplaceValue,{"1 - コピー - コピー - コピー"}),
    重複された列3 = Table.DuplicateColumn(置き換えられた値1, "1 - コピー - コピー - コピー", "1 - コピー - コピー - コピー - コピー"),
    上方向へコピー済み1 = Table.FillUp(重複された列3,{"1 - コピー - コピー - コピー - コピー"}),
    #"名前が変更された列 " = Table.RenameColumns(上方向へコピー済み1,{{"1 - コピー", "2"}, {"1 - コピー - コピー", "3"}, {"1 - コピー - コピー - コピー", "4"}, {"1 - コピー - コピー - コピー - コピー", "5"}})
in
    #"名前が変更された列 "

コア部分の説明

フィルの「漏れ」とその対策

フィルの動作はnullがある限り値をコピーし続けます
下記のように
目的の境界に停止させるような文字列がない場合
何も考えず上フィルをかけた場合このように
内容が外に漏れだします(4／21～4／30に5月が入ってしまってる)

これの解決方法は目的の境界に
何かしらのnull以外が入る列を作ってやり
フィルの漏出を止めてやります

条件列での仕切り作成

こういった内容を解決する方法として
条件列で境界を作ってやります
今回は境界位置に一番近く位置が動きにくい
X月1日を仮の境界として処理します(こういう安定した境界を見つけるのが重要)
方法としては条件列で日が1のところに「〇(置換の邪魔をしなければ何でもよい)」を入れる操作をします

フィルの前にこの条件列で

簡略化

今回の元データはコード行削減のために月日の列にもフィルをかけて
処理を減らしています

let
    //元データJSON
    X0 = "7ZY7DsIwEAXv4joS6+df4CqYMkdIhbg7LlIg4mIKChylsOREI6eY7Mj3p6trWkqsay42u1t7DGbmprbJaUntjffuNRFOkAuQi5BLkMuQK5CbIXftcTJZvVhsy3/SMnaqoBVBK4JWBK0IWhG0ImhF0Iq6VvZcgD6oDvhVhkEXUAU0AUVAD1CDpxr+KlbbkKfvIR8zWZ2/+YzVkLEKdErOqP0ualsM8i4GQ6btKPewftQebw==",

    //テーブル変換部
    X1 = Binary.FromText(X0),
    X2 =Binary.Decompress(X1,Compression.Deflate),
    X3 =Table.FromRecords(Json.Document(X2,932)),
    //テキスト化のためにいれたスペース削除でNULL化
    置き換えられた値 = Table.ReplaceValue(X3,"　",null,Replacer.ReplaceValue,{"年月"}),

    //コア部
    追加された条件列 = Table.AddColumn(置き換えられた値, "A", each if [日] = "1" then "〇" else [年月]),
    //上下方向へフィル、逆だと1日がおかしくなります
    下方向へコピー済み = Table.FillDown(追加された条件列,{"A"}),
    上方向へコピー済み = Table.FillUp(下方向へコピー済み,{"年月"}),
    //Aと年月から必要な物だけ抜取りカスタムへ入れる
    追加された条件列1 = Table.AddColumn(上方向へコピー済み, "カスタム", each if [A] = null then [年月] else if [A] = "〇" then [年月] else [A])
in
    追加された条件列1

ペシャルサンクス

テーブルをバイナリ（JSON）化したり、テーブルに戻したり
https://qiita.com/tanuki_phoenix/items/5f9991ac2712f91f5fda