【PowerQuery】フォルダパス列から最終保存フォルダを抜き出した列を作成

大した内容ではないのですが、備忘も含め書いておこうと思います。

（１） やりたいこと

下図の通り、フォルダからPowerQueryエディターへファイルを読み込んだ後、そのファイルの一覧のフォルダパスから最終保存先フォルダのみを新たな列に抜き出す方法となります。単純に「列の分割」で区切り記号毎に分割してしまうと各行のフォルダ数が合わない場合（下図3,4行目のように）はうまくいきませんのでそこも考慮した書き方にしています。

（２） 実践

下図はフォルダからファイルを取り込んだ後、適当な列を削除した状態です。

列の追加タブ → カスタム列 をクリックします。

カスタム列の式に以下の式を入力しOKをクリックします。

= List.First( List.Skip( List.Reverse( Text.Split( [Folder Path],"\"))))　

下図の通り最終保存フォルダのみを抜き出した列ができました。

（３） 解説

カスタム列の追加の式をText.Split関数からひとつずつ増やしていく形で、以下の画像上で簡単に解説します。

※下図式のTable.Addcolumn～eachまではカスタム列の追加により自動生成されるもの

以上です。

【PowerQuery】更新するたびにランダムな出社パターンの勤務表を作成

最初に言っておくと、今回の記事は例によって本来PowerQueryでやるようなものではありません。元々Excelワークシート関数でやったことをPowerQueryでもできるかなと思い試しました。ただ、試行錯誤しながらやった中でM関数の使い方等勉強になったところが色々あり、他で活かせるかなと思っています。

（１） やりたいこと

ある月(又は期間)の勤務日程を決める際、一日毎の出社率が決まっており、日ごとに出社する人と在宅勤務する人とに分けたい場合に、決められた条件でランダムに振り分けたいというものです。最終的な形は下図の通りです。

（２） 事前準備

上図右側のような設定入力用のテーブル３つ（メンバー、出社率、基準日）をExcelワークシート上に作成し、それぞれをデータ→テーブル又は範囲から　にてPowerQueryエディターに取り込みます。読み込みはすべて「接続専用」とします。

下図は３テーブルを取り込み後のPowerQueryエディターです。

出社率クエリを選択し、値の上で右クリック→ドリルダウンをクリックします。

単一の数値となります。0.5＝Excelワークシート上の50%です。

基準日クエリを選択し、同様にドリルダウンします。

単一の日付となります。

※参考

（３） コード

ここで先に全体のコードを書いておきます。前項までの準備をした後に詳細エディターにそのまま貼付けて出力可能です。詳しくは次の（４）で説明していきます。

let
    人数 = List.Count(メンバー[メンバー]),
       
    出社可能人数 =  Number.RoundDown( 人数 * 出社率 ),
    
    //  ○ = 出社　□ = 在宅
    ソース =  List.Generate(
                           ()=> [ 日付 = 基準日, 
                                 mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数) 
                                 ],  　

                           each [日付] <= Date.EndOfMonth(基準日),
                           each [ 日付 = Date.AddDays([日付],1) , 
                                  mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数) 
                                ]
　　　　　　　　　　         ),

    テーブル変換 = Table.FromRecords(ソース),

    // 1文字ずつ列を分割、列名はメンバー
    mark列の分割 = Table.SplitColumn(テーブル変換, "mark", Splitter.SplitTextByRepeatedLengths(1) , メンバー[メンバー]),

    ピボット解除された他の列 = Table.UnpivotOtherColumns(mark列の分割, {"日付"}, "属性", "値"),

    // 前ステップの列からテーブルへ　乱数列はList.Random関数にて前ステップのリストカウント分を作成し追加
    乱数列を追加_メンバー除く = Table.FromColumns(
                                                 {ピボット解除された他の列[日付],
                                                  ピボット解除された他の列[値],
                                                  List.Random (List.Count(ピボット解除された他の列[日付]))
                                                 }
                                                ,{"日付","mark","乱数"}
                                                
                                               ),

    // 日付列、乱数列の順で昇順にソート
    並べ替えられた行 = Table.Sort(乱数列を追加_メンバー除く,{{"日付", Order.Ascending}, {"乱数", Order.Ascending}}),

    削除された列 = Table.RemoveColumns(並べ替えられた行,{"乱数"}),

    メンバー追加 = Table.FromColumns(
                                     { 削除された列[日付],
                                       ピボット解除された他の列[属性],
                                       削除された列[mark]
                                     }
                                       ,{"日付","メンバー","mark"}
                                   ),
    
    ピボットされた列 = Table.Pivot(メンバー追加, List.Distinct(メンバー追加[メンバー]), "メンバー", "mark")
    
    
in
    ピボットされた列

（４） 実践・経過説明

PowerQueryエディターでホーム→新しいソース（右端）→ その他のソース → 空のクエリをクリックします。

空のクエリが作成されます。

ホームタブ → 詳細エディター をクリックします。

下図のコードを入力し完了をクリックします。（ステップの追加からひとつずつかいてもＯＫです）

最初の「人数」「出社可能人数」のステップはそれ以下で使用するために定義したものです。出社可能人数の計算は人数に出社率を乗じた結果をラウンドダウンしていますがここは必要に応じて変えてください。

その後の「ソース」のステップが今回ひとつのポイントとなります。List.Generate関数を使ってレコードのリストを作成します。以下説明

List.Generate(   ()=> [ 日付 = 基準日,                                                                                                                                  mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数)  ],                                                                                                                                                           each [日付] <= Date.EndOfMonth(基準日),                                                                                       each [ 日付 = Date.AddDays([日付],1) , 
                 mark = Text.Repeat("○", 出社可能人数)& Text.Repeat("□", 人数-出社可能人数) 
                ]
　　　 　　　)

初期値(initial)

日付 = 基準日（ワークシート上に入力した年月日となります）                                  mark = 出社可能人数分の”○” と 人数から出社可能人数を差し引いた数分の”□”を                        くっつけたテキスト

　をレコードとして設定します。

条件(condition)

Date.EndOfMonth関数を用い、日付を初期値(基準日)の月の月末日以下に設定。

次の値(next)

日付 = 日付+1日 を加算した日付                                                                                   mark = 出社可能人数分の”○” と 人数から出社可能人数を差し引いた数分の”□”を                        くっつけたテキスト

　をレコードとして設定します。

初期値と次の値で記した、mark の内容はまったく同じです。つまり、日付は基準日からその月の月末日まで、mark はすべての日付で同じ値 のレコードのリストが作成されます。以降の画像を見た方がわかり易いと思います。

下図が先ほどのList.Generateによって作成されたリストです。カーソルを1行目におき下部に中身が表示された状態にしています。日付とmarkが1セットのフィールドとなったレコードのリストで日付が2021/9/1 から2021/9/30 までの30セットできています。

次に、数式バーの左のfx(ステップの追加）をクリックし次の数式を入力します。

Table.FromRecords(ソース)

前ステップのレコードをテーブルに変換する記述です。

次にmark列の値を一文字毎に列を分割し、列名をメンバー名にします。

fxステップの追加をクリックし次の数式を入力します。

Table.SplitColumn(テーブル変換, "mark", Splitter.SplitTextByRepeatedLengths(1) , メンバー[メンバー])

Splitter.SplitTextByRepeatedLengths に1を設定することでmark列の値を1文字ずつ列分割することができました。この結果、下図の通りとなりますがこの時点ではまだランダムな並びにはなっていません。

次に、日付列を選択した状態で、右クリック → その他の列のピボット解除 をクリックします。

下図の通りピボット解除されます。

次に、再びfx(ステップの追加)をクリックし、次の数式を入力します。

Table.FromColumns(   {ピボット解除された他の列[日付], 
                                     ピボット解除された他の列[値],
                                     List.Random (List.Count(ピボット解除された他の列[日付]))
                                     }
                                      ,{"日付","mark","乱数"}
                                                
                                  )

Table.FromColumns関数で3つの列からテーブルを作成します。最初の2つは前ステップの日付と値(mark）です。3つ目の列は、List.Randomで作成した乱数のリストです。List.Ramdomの引数で乱数をいくつ作るか指定する必要がありますので前ステップの日付の数分となるよう設定しています。Table.FromColumnsの最後の部分でテーブルの列名をそれぞれ"日付","mark","乱数"と指定しています。結果、下図の通りとなります。メンバー名については後のステップで追加します。

次に日付毎にランダムなmarkの並びにするために日付、乱数列の順番で昇順にソートします。各列毎に並び替えボタンをポチポチするか、もしくはステップの追加で次の数式を入力します。

Table.Sort(乱数列を追加_メンバー除く,{{"日付", Order.Ascending}, {"乱数", Order.Ascending}})

結果的に下図の通り、日付順にmarkがランダムに並んだ状態となります。

乱数列は不要となりますので削除します。

ステップの追加(fx)をクリックして次の数式を入力します。

Table.FromColumns(    { 削除された列[日付],
                                       ピボット解除された他の列[属性],
                                       削除された列[mark]
                                     }
                                       ,{"日付","メンバー","mark"}
                                   )

少し前のステップと同じTable.FromColumns関数で3つの列からテーブルを作ります。前ステップの日付、mark列にピボット解除した際の属性（メンバー名）を追加する形です。下図の通りとなります。

最後にメンバー列を選択した状態で、変換タブ → 列のピボット をクリックします。

下図の通り値列をmark、値の集計関数は修正しないとしOKをクリックします。

これで完成です。

（５）動作確認

下図はワークシートにテーブルとして読込んだ状態です。テーブル上で右クリックし、更新してみます。

○と□の並びがランダムな並び置き換わりました。

次にメンバーをひとり追加（マイケル）、出社率を30％に変更、基準日を2021/10/1に変更した状態にしてみます。出社可能人数は2名（7名×30%）です。

テーブルを更新すると下図の通り、各項目が追加・変更され、日ごとに〇が２つ、□が5つのマークがランダムに振られた状態に変わりました。

（６）別解　※２０２１/１０/２９追記

もう少しシンプルな書き方を考えたので追記しておきます。

List.generateは日付のみのリストを作成、その後必要な日数×人数分の日付・メンバー・markのリストを作成してから結合してピボット解除

let
    人数 = List.Count(メンバー[メンバー]),
    
    
    出社可能人数 =  Number.RoundDown( List.Count(メンバー[メンバー])*出社率 ),
    
    //  日付のリストを作成
    ソース = List.Generate(
                           ()=>  基準日, 
                               
                           each  _ <= Date.EndOfMonth(基準日),
                           each  Date.AddDays(_,1) 

                          ),

    
   

// 日数×人数分の日付リスト
    日付リスト = List.Sort( List.Repeat( ソース,人数)),

    メンバーリスト = List.Repeat(メンバー[メンバー],List.Count(ソース)),

    // 1日分のmarkリスト
    markリスト_日 = Text.ToList( 
        　　　　　　　　　　　　　Text.Repeat("○",出社可能人数)
        　　　　　　　　　　　　 &Text.Repeat("□",人数-出社可能人数)
        　　　　　　　　　　　　 ),

    // 日数×人数分のランダムmarkリスト
    markリスト = List.Repeat(
                       Table.Sort( 
                              Table.FromColumns(
                                                                { markリスト_日,
                                                                  List.Random( List.Count(markリスト_日))
                                                                } ,{"mark","乱数"})
                                         ,{"乱数"}
                                       )[mark]   // 乱数列で並び替え後、mark列のみ抜き出し

                         　                  ,List.Count(ソース)  // 日付分繰り返し
                                               ),

    リスト結合 = Table.FromColumns(
        　　　　　　　　　　　　　　　{日付リスト,
        　　　　　　　　　　　　　　　 メンバーリスト,
                                                                markリスト
                                                                },{"日付","メンバー","mark"}),

    ピボットされた列 = Table.Pivot(リスト結合, List.Distinct(リスト結合[メンバー])
                                                         , "メンバー", "mark"
                                                        )
    
in
    ピボットされた列

【Excel】RANDARRAY関数等を使ってランダムな配列を作成

RANDARRAY関数(Microsoft365で使用可)を使ってみました。実務で乱数を使うことはあまりないのですが、テスト用のデータを作成する時などに役立ちそうです。

support.microsoft.com

以下、それぞれ20行くらいで作成することとします。

（１） 1,000～10,000 間のランダムな数値

A1の数式

=RANDARRAY(20,1,1000,10000,1)

（２） 100～10,000 間のランダムな数値(100円単位)

A1の数式

=RANDARRAY(20,1,1,100,1)*100

 

（３）2021年4月1日～2021年9月30日 間のランダムな日付

A1の数式

=RANDARRAY(20,1,44287,44469,1)

※2021/4/1のシリアル値 ＝44287 　、2021/9/30のシリアル値 ＝44469

　A列の表示形式を日付にしています。

 

（４） A ～ Z 間のランダムな値

A1の数式

=CHAR(RANDARRAY(20,1,65,90))

CHAR関数で文字コードを文字にしています。65 ＝ A 　で 90＝Z です。

（５）A ～ Z をランダムに表示（すべての文字を使用）

A1の数式

=SORTBY(CHAR(SEQUENCE(26,1,65)),RANDARRAY(26))

連続した数値の配列を作るSEQUENCE関数を使って、65～90の連番を作り、それをCHAR関数でアルファベットに変換。更に配列を並び替えるSORTBY関数の引数をRANDARRAY関数を使ってランダムに並べ替えています。

 

（６） 文字列のリストをランダムに表示

まず、F列に対象のリストを文字列として入力します。E列にはF列に対応する連番を入れますが、E1セルに次の数式を入力することでF列の入力セルに対して可変で付番することができます。

E1の数式

=SEQUENCE(COUNTA(F:F),1,1,1)

A1の数式

=VLOOKUP(RANDARRAY(20,1,1,MAX(E1#),1),OFFSET(E1,0,0,COUNTA(E:E),2),2,0)

RANDARRAY関数でE列の数字のランダムなリストを作ってから、VLOOKUP関数でその数字に対応するF列の文字列を表示しました。VLOOKUP関数の引数の範囲はOFFSET関数で可変にしています。

 

（７） メンバーリストからランダムなトーナメント表を作る

AL列のメンバーリストは文字入力。AK列の連番は⑥と同じです。

AK2の数式

=SEQUENCE(COUNTA(AL:AL),1,1,1)

AN列にランダムに表示した結果を左トーナメント表の18行目の各セルで参照しているだけです。（トーナメント表の18行目の名前セルはそれぞれセル結合しており配列で直接表示できないため）

AN2の数式

=VLOOKUP(SORTBY(AK2#,RANDARRAY(MAX(AK2#))),OFFSET(AK2,0,0,COUNTA(AK:AK),2),2,0)

この数式は⑤⑥を合わせたものなので説明は省略します。

 

（８） メンバーリストから指定したチーム数にランダムに振り分ける

E列のメンバーリストは文字入力。D列の連番は⑥⑦と同じです。

B列の名前は⑦と同じ要領でE列のリストをランダム表示しています。

H2セルのチーム数は手入力します。ここで入力したチーム数によってA列のチームが自動で振られるようにA2セルに数式を入力します。チームはアルファベットとし、2チームであればA、B  。3チームであればA、B、C となるようにしています。

数式の説明の前に、チーム数やメンバーを変更した時の動きを確認します。

【チーム数を6に変更】

A～Fの6チーム。A、Bがそれぞれ4名、C～Fがそれぞれ3名ずつに分けられました。

【更に1名追加（21人目の比嘉さん)、チーム数は6のまま】

A列、B列にも自動で追加されました。Cの人数が3→4名に増えました。

D2の数式

=SEQUENCE(COUNTA(E:E),1,1,1)

B2の数式

=VLOOKUP(SORTBY(D2#,RANDARRAY(MAX(D2#))),OFFSET(D2,0,0,COUNTA(D:D),2),2,0)

ここまでは⑦までと同じです。

A2の数式

=SORT(MID(MID(REPT(TEXTJOIN("",1,CHAR(SEQUENCE(H2,1,65,1))),ROUNDUP(MAX(D2#)/H2,0)),1,MAX(D2#)),SEQUENCE(LEN(MID(REPT(TEXTJOIN("",1,CHAR(SEQUENCE(H2,1,65,1))),ROUNDUP(MAX(D2#)/H2,0)),1,MAX(D2#)))),1))

かなり長くなってしまったので、何回かに分けて式を足していく形で以下にて説明したいと思います。（前後の画像の数式の文字色を合わせています）

＜その１＞

※元の体裁は残しつつ、新たにI5セルに数式を入れています。

まず、SEQUENCE関数でH2に入力されたチーム数分の65から始まる連番を作り、これにCHAR関数をかけることでアルファベットにします。さらにその結果をTEXTJOIN関数でつなぎ合わせます。I5セルの結果の通り、AからFまでの6つのアルファベットが出ました。

＜その２＞

先ほどの数式にREPT関数を追加します。この関数は指定した回数のテキストを繰り返すものです。繰り返すテキストは＜その１＞の結果の文字列ABCDEFです。回数ですが、繰り返した結果の文字数とメンバー数を合わせることできないので、メンバー数を下回らない最低限の文字数となるように、メンバー数(MAX関数でD2の配列の最大値）をチーム数で除した結果をROUNDUPした数を繰り返すことにします。今回は21÷6=3.5 ・・4回 となり、I5セルはABCDEF×4回の文字列(24文字)が表示された状態となります。

＜その３＞

次は、＜その２＞で出した文字列(24文字）から必要なメンバー数分の文字列(21文字）を抜き出します。MID関数を使って1文字目からメンバー数(MAX関数でD2の配列の最大値）までを抜き出します。I5セルの結果は21文字となりました。

＜その４＞

＜その３＞の結果はあくまで1セル内の21文字なのでこれを各セルに展開し、ランダム表示したメンバーに割り当ててあげなければなりません。1セルの文字列を分解するには、MID関数、SEQUENCE関数、LEN関数を使います。

まず、MID関数の対象文字列は＜その３＞の数式です。（上図の最初の下線部分）次の第２引数は、まず、＜その３＞の数式（上図の2番目の下線部分）にLEN関数で文字数を数えます。それを更にSEQUENCE関数で連番にします。MID関数の第３引数の文字数を1とすることで、1文字1セルに分割することができました。

最後に、SORT関数で囲ってアルファベット順に並べ変えます。これが最終結果(A3セル)となります。

以上となります。

【PowerQuery】二つの日付間の経過月数を算出する

大した内容ではありませんが、計算方法の備忘として。

１．やりたいこと

２．前提条件

• 基データの中に基準日が含まれていないものとして、入力用のテーブルを用意する。(基データに基準日がある場合は不要）
• 経過月数のカウントはあくまで月が変わったらひと月とする。日付は見ない。（例：計上日2021/9/1、基準日2021/9/30 であれば経過月数は0）

３．実践

基データテーブル(DATAクエリ）と基準日テーブル(基準日クエリ）をPowerQueryエディターへ取込む

基準日クエリを選択し、データセル箇所で右クリック→ドリルダウンをクリックする。

※今回基準日は2021/9/30としていますが、変更する場合はワークシート上のテーブルの値を直接書き換えます。

日付時刻ツールが表示され、「基準日」という1つの日付となりました。

DATAクエリを選択。列の追加タブ→カスタム列　をクリックします。

カスタム列の式に以下の式を入力します。

(Date.Year(基準日) - Date.Year([計上日]))*12 + 
Date.Month(基準日) - Date.Month([計上日])

 

以下２つの例で数式を説明します。

【例1】基準日が2021年9月30日、計上日が2021年6月30日 であった場合

数式の1行目で年の差分の月数を算出します。

=　(Date.Year(基準日) - Date.Year([計上日])) * 12

=  ( 2021  - 2021 ) *12

=  0

 

数式の2行目で月の差分の月数を算出します。

=　Date.Month(基準日) - Date.Month([計上日])

=   9  -  6

=  3

 

0　+　3　＝　3 ヶ月となります。

【例2】基準日が2021年9月30日、計上日が2019年10月1日 であった場合

数式の1行目で年の差分の月数を算出します。

=　(Date.Year(基準日) - Date.Year([計上日])) * 12

=  ( 2021  - 2019 ) *12

=  24

 

数式の2行目で月の差分の月数を算出します。

=　Date.Month(基準日) - Date.Month([計上日])

=   9  -  10

=  -1

 

24　+　-1　＝　23 ヶ月となります。

 

経過月数列が追加されました。

以上です。

【Power Automate Desktop】選択した期間・決算月からカレンダーデータ（Excelファイル）を作成する

１．やりたいこと

下図に記載の通り、PADフローを実行して対象期間及び決算月を選択すると、対象期間の年月日、曜日、年度、期(上下）、四半期をExcelファイルに転記してデスクトップに保存します。年度、期、四半期は企業によって様々ですので決算月を選択することでこれに対応できるようにします。

２．フロー作成の要件(概要）

• ダイアログで対象期間(開始日/終了日)、決算月(月のリスト）を選択する。
• 対象期間選択時の初期値は現在日付とする。
• 上記ダイアログでCancelした場合はフローを停止。
• ループ条件(開始日<=終了日)でエクセルに日付を書き込む。
• 曜日、年度、期、四半期列はPAD変数を用いてワークシート関数を書き込む。
• デスクトップに名前を付けてエクセルを保存する。
• ファイル名は、” Calendar(開始日_終了日).xlsx ”とする。

３．実践① フローの作成 ～ ダイアログの設定等

コンソール画面から 新しいフロー をクリックします。

任意のフロー名を入れて作成をクリックします。

デザイナー画面が立ち上がります。

【現在の日時を取得】

アクション → 日時 → 現在の日時を取得します　をダブルクリック（又は画面中央にドラッグ＆ドロップ）します。

下図の通り選択し保存します。生成された変数はデフォルトのCurrentDateTimeのままとします。

【datetime をテキスト(yyyy/M/d形式）に変換】

アクション → テキスト → datetimeをテキストに変換をダブルクリックします。

下図の通り選択・入力し保存します。

※登録済の変数は右端の{ⅹ}をクリックして選択可能です。

生成された変数は、CurrentDate とします。

変数名をクリックすると変更できます。変数名の頭と末尾は%で囲みます。(確定すると%の表示は消えます）

【対象期間選択のダイアログ設定】

アクション → メッセージボックス → 日付の選択ダイアログを表示 をダブルクリックします。

下図の通り選択・入力し保存します。

１つ目の日付を StartDate、２つ目の日付を EndDate としています。

【日付選択のダイアログ入力がキャンセルされた場合の設定】

日付選択時にキャンセルされた場合はメッセージボックスを表示後、フローを停止します。

アクション → 条件 → If　をダブルクリックします。

下図の通り選択・入力し保存します。

アクション → メッセージボックス → メッセージを表示をダブルクリックします。

下図の通り選択・入力し保存します。表示するメッセージは任意の文言で構いません。

アクション → フリーコントロール → フローを停止する をダブルクリックします。

下図の通り選択し保存します。If の中身はここまでです。

ここまでのフローは下図の通りです。

【決算月を選択するリストのダイアログを設定】

アクション → メッセージボックス → リストから選択ダイアログを表示 をダブルクリックします。

下図の通り選択・入力し保存します。

選択元のリストは、一月ずつ改行して入力します。一般に3月決算の企業が多いと思いますので、リストは3月始まりにしましたが、順番は変えても問題ありません。

選択したリストはテキストとして、ClosingMonth という変数に格納します。

下図２つ目の変数(選択する項目のインデックス番号）は今回は使わない為オフとしています。３つ目の変数名は先ほどと同じ名称 ButtonPressed の為ここで上書きされますがフローに影響はないのでそのままで大丈夫です。

【決算月選択のダイアログ入力がキャンセルされた場合の設定】

決算月選択時にキャンセルされた場合はメッセージボックスを表示後、フローを停止します。ここは先ほどの日付選択時のキャンセル時とまった同じ操作・入力となりますので画面説明は省略します。

設定後のフローは下図の通りとなります。

４．実践② Excelの起動 ～ 列見出しの書き込み

アクション → Excelの起動 をダブルクリックします。

下図の通り選択・入力し保存します。インスタンスを表示する：をオンにすると実行時に時間が掛かる為、オフにしています。

アクション → Excel →Excelワークシートに書き込み　をダブルクリックします。

下図の通り選択・入力し保存します。

2～5列目も同様にExcelワークシートに書き込みアクションにて書き込む値と列のみ変えて設定します。（画面は省略）

この部分のフローは下図の通りです。

５．実践③ ループ処理／Excelワークシートへの書き込み

ここからは日付等をExcelへ書き込むための設定をしていきます。

【ループ処理用の変数を2つ（行と日付のカウンター）設定】

アクション → 変数 → {x}変数の設定　をダブルクリックします。

下図の通り入力し保存します。これは書き込む行のカウンター用です。

同様にループ用の日付変数も設定します。これは日付の書き込みにも使います。

【ループ処理の設定】

アクション → ループ → ループ条件　をダブルクリックします。

下図の通り入力し保存します。

先ほど設定したVardateがEndDate以下である限りループ処理が続きます。

ここからループ処理の中身を設定していきます。

【ループ用日付を書込み用日付に変換】

アクション → テキスト → datetime をテキストに変換　をダブルクリックします。

下図の通り入力し保存します。

【各項目をExcelシートへ書き込む】

アクション → Excel → Excelワークシートに書き込み　をダブルクリックします。

■日付の書き込み

※以下、Excelワークシート関数を書き込んでいきますが数式自体の説明は今回省略します。数式の一部はこちらの記事を参考にさせていただきました。

■曜日の書き込み

書き込む値：

=TEXT(A%RowCount%,"AAA")

実際にExcelに書き込まれる数式（初回）

=TEXT(A2,"AAA")

■年度の書き込み

書き込む値：

=TEXT(YEAR(EDATE(A%RowCount%,1-IF(%ClosingMonth%=12,1,%ClosingMonth%+1))),"@")

実際にExcelに書き込まれる数式（決算月3月の初回）

=TEXT(YEAR(EDATE(A2,1-IF(3=12,1,3+1))),"@")

※計算結果を値にするためにTEXT(　"@")をいれていますが、数値としたい場合はこの部分は不要となります。

■期（上期・下期）の書き込み

書き込む値：

=IF(A%RowCount%<EOMONTH(DATE(C%RowCount%,IF(%ClosingMonth%=12,1,%ClosingMonth%+1),1),5)+1,"上期","下期")

実際にExcelに書き込まれる数式（決算月3月の初回）

=IF(A2<EOMONTH(DATE(C2,IF(3=12,1,3+1),1),5)+1,"上期","下期")

■四半期の書き込み

書き込む値：

=INT(MOD(MONTH(A%RowCount%)-IF(%ClosingMonth%=12,1,%ClosingMonth%+1),12)/3)+1&"Q"

実際にExcelに書き込まれる数式（決算月3月の初回）

=INT(MOD(MONTH(A2)-IF(3=12,1,3+1),12)/3)+1&"Q"

書き込み処理はここまでとなります。

【各変数のカウントアップ】

アクション → 日時 → 加算する日時 をダブルクリックします。

下図の通り入力し保存します。

アクション → 変数 → 変数を大きくする　をダブルクリックします。

下図の通り入力し保存します。

ここまででループ処理の中身は完成です。フローは下図の通りです。

６．実践④ Excelファイルの保存

【ファイル名付加用の日付を取得】

アクション → テキスト → datetimeをテキストに変換 をダブルクリックします。

下図の通り入力し保存します。

次も同様です。

Excelファイルの保存先をデスクトップとするため、特別なフォルダを取得します。

アクション → フォルダ → 特別なフォルダーを取得

下図の通り入力し保存します。特別なフォルダーの名前でデスクトップを選択すると、次のパスは自身のデスクトップパスが自動表示されます。

【Excelファイルに名前を付けて保存する】

アクション → Excel → Excelを閉じる をダブルクリックします。

下図の通り入力し保存します。

ドキュメント パス：　%SpecialFolderPath%\Calendar(%FilenameDate1%_%FilenameDate2%).xlsx

これでフローの設定はすべて完了です。最後の部分のフローは下図の通りです。

画面上の保存ボタンをクリックしてフローを保存後、デザイナー画面を閉じます。

７．フローの実行

コンソール画面から実行ボタンをクリックします。

対象期間の選択ダイアログが表示されます。初期値は何れも現在日付です。

対象期間を入力しOKをクリックします。

今回は2021年4月1日～2022年3月31の1年間としました。

決算月を選択ダイアログが表示されます。

今回は3月決算としますのでそのままOKをクリックします。月を変更する場合は右端のボタンをクリックして表示されたリストから選択します。

暫くすると処理が完了し、画面右下に完了通知が表示されます。(通知設定ONの場合）

今回1年分の処理時間は 32秒でした。

デスクトップにファイルが保存されました。開いてみます。

列幅が元のままできれいに表示されていないので適当に自動調整します。

最下行まで正しく作成されていることが確認できました。

下図は最後の入力セルE366にカーソルを置いた状態です。数式バーで設定した数式が入力されています。PAD変数もきちんと置き換わっています。

８．さいごに

この記事を書いている途中で疑問が思いつきました。曜日、年度、期、四半期を数式ではなく計算した結果のテキストとして書き込むにはどうしたらよいか。

答えは簡単でした。ループ処理のところで、下図のように一旦数式をワークシートに書き込んだあと、同じ個所を読み取って変数に格納し、再度同じ個所にその変数を書き込めばテキスト化できました。

ただ、読み取り書き込みの処理が増えますので当然ながら処理時間も倍くらいに増えてしまいます。数式で処理するフローとは別にサブフローでテキスト化のフローを作っておいて、フローの最初にダイアログ選択を入れて分岐させるのがよいかもしれません。

以上となります。

【PowerQuery】フォルダからのデータ取得で列名が変動するデータを取込む方法

１．やりたいこと

下図の通り、「月別データ」フォルダに保管されている月別のファイルをPowerQueryで取込みたいのですが、このデータは月によって列の部門が変動します。決まったファイルをまとめて取込む場合は問題ないのですが、毎月実績が出た後にその月のデータをフォルダに保管して常に累計のデータを出すような場合、GUI操作で進めていくと前月までになかった部門が新たにでた時にデータが取り込まれなくなってしまうのです。これを解消する方法を考えてみました。

２．列名が変動するとなぜ取込めなくなるか

通常、フォルダのファイルを展開する場合、下図のように展開する列を選択します。この後OKをクリックしたら展開されるのですが、この操作はあくまでこの時点で対象となっているファイルに含まれる列となります。このままだと例えば新たにフォルダに追加したファイルの列に「G部門」や「H部門」があった場合、これらの列は展開する対象から漏れてしまうことになるのです。

（上記展開した際に生成されるM関数）

３．どうすればよいか

単純に前述のTable.ExpandTableColumn関数の列名の引数を可変となるようにしてあげればよいのではないかと思いました。今回紹介する方法が最適解かどうかはわかりませんが、次項で説明していきます。

４．実践

取込み用のExcelファイルを作成し、メニューバー → データ → データの取得 → ファイルから → フォルダから　をクリックします。

フォルダーのダイアログが表示されたら右端の参照から取込み対象のフォルダを選択します。今回はPQテストフォルダの中にある「月別データ」フォルダとします。

フォルダに保管されているファイル一覧が表示されるので、データの変換をクリックします。

PowerQueryエディターが立ち上がります。通常、ここでContent列の見出し右端の矢印ボタンをクリックしてFileの結合を行いますがその場合ヘルパークエリ等が出てきてごちゃごちゃするので、今回はステップの追加から直接コードを書くことにします。

数式バー左のFx（ステップの追加）をクリックします。下図のように適用したステップにカスタム1が追加され、数式バーに前ステップ名が入った状態になります。

数式を次の通り変更します。

【変更前】

　= ソース

【変更後】

　= Table.AddColumn(ソース, "FileChanges", each 　　Table.PromoteHeaders(Excel.Workbook([Content], null, true)　　　{[Item="Sheet1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]))

 

FileChanges列が追加されました。ためしにFileChanges列の1行目のTableのところにカーソルを置くと画面下にファイルのプレビューが表示されます。

※ステップ名 カスタム1はFileChanges_Addに変更しています。

ホームタブ → 詳細エディター をクリックします。

詳細エディターが開いたら、まず下図の通り let の下に1行を追記します。

「FolderPath = 」の後は元々の「ソース=Folder.Files( 」の後のフォルダパスと同じものを入れます。その後、元々の「ソース=Folder.Files( 」の後の中身を「FolderPath  」に書き換えます。（関数の記述を簡略化しているだけです）

続けて、3行目のFileChanges_Add の末尾に「 , 」を入力してから次の数式を入力します。

AllFilesColNames =　　　　　　　　　　　　　　　　　　　　　　　　　　　　List.Accumulate(
 {1..List.Count(FileChanges_Add[Content])-1},　　　　　　　   {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{0}]}[FileChanges])},
 　(state,current)=> state & {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{current}]}[FileChanges])}
　　　　　　    )

対象フォルダに保管されたすべてのファイルの列名をList化するためにList.Accumulate関数を使いました。以下、簡単に説明します。

第1引数 List：　{1..List.Count(FileChanges_Add[Content])-1}　

　1 で始まり、FileChanges_AddのContent列の数から1を引いた数まで、今回の例だと1,2,3 となります。1番目を表す 0 は次の引数(seed)を出力結果に含める為、ここでは除いています。

第2引数 seed： {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{0}]}[FileChanges])}

　FileChanges_Addの1行目({0}で表現）のテーブルdataからTable.ColumnNamesで列名を抜いています。これが初期値となり、次の第3引数で最初の state となります。

第3引数 accumlator(function)： (state,current)=> state & {Table.ColumnNames(FileChanges_Add{[#"Folder Path"=FolderPath&"\",Name=FileChanges_Add[Name]{current}]}[FileChanges])}

state,current で関数を設定しています。seedの数式と異なるのは、[Name]の後の{ } の中を current としており、ここに第1引数のListが代入され計算されます。出力結果は state&としていますので初期値を含めたすべてのファイルの列名がList化されることになります。

下図の通り、記述し　in　の後を AllFilesColNames に変えて完了をクリックします。

すべてのファイルの列名が入れ子となったリストの状態となります。

ファイルタブ → テーブルへの変換 をクリックします。

下図の状態でOKをクリックします。

テーブルに変換されました。この時点では各Listが入れ子となったままです。

列見出し右の矢印ボタンから 新しい行に展開する をクリックします。

展開されました。

重複された行を削除するため、ホームタブ → 行の削除 → 重複の削除をクリックします。

重複が削除されすべてのファイルで一意の列名となりました。あとはこれを参照してテーブルを展開できれば目的達成です。

GUI操作で展開はできないので、数式バー左のFx（ステップの追加）をクリックし直接コードを書きます。

数式を次の通り変更します。

【変更前】

　= 削除された重複

【変更後】

　= Table.ExpandTableColumn(FileChanges_Add, "FileChanges", 削除された重複[Column1], 削除された重複[Column1])

FileChanges_Addのステップを参照して展開するのがポイントです。

 

きちんと展開されました。

不要な列を削除します。

これで完成です。

年月、科目の列を選択した状態でその他の列のピボット解除をすると

下図のようなデータとなりますのでこれを使った集計等ができるようになります。

この後、新たな列が含まれた別ファイルをフォルダに追加してもクエリの更新でテーブルに反映されるようになります。

以上です。

【Power Automate Desktop】選択したフォルダ内のファイル情報一覧をエクセルへ書き出す(+各ファイル名にハイパーリンクを設定)

１．やりたいこと

下図に記載の通り、選択したフォルダ内（サブファルダを含む）の各種ファイルの情報を取得してエクセルに書き出し保存します。また、各ファイル名には各々のファイルパスをハイパーリンクとして設定し、ファイル名をクリックすると各ファイルが開くようにします。で、この一覧を使って何をしたいか・何を管理したいかについては明確には考えていません。今後、電子帳簿保存法の関係等で使える場面が出てくるかもしれません。

 【PADフロー作成のポイント】

・各ファイル情報は、For eachでCurrentItemのプロパティから読み取る。

・ハイパーリンク、エクセルの列幅調整等は「キーの送信」で処理。

・エクセルファイル保存時に使用する元フォルダの名前は、「テキストの解析」等

　で抜き出す。

２．実践①　フロー作成　～　エクセル見出し行の書き込み・変数の設定

トップ画面から、新しいフローをクリックします。

任意の名前を付けて作成ボタンをクリックします。

画面左のアクション一覧からフォルダ→特別なフォルダーを取得 をダブルクリック（又は真ん中のフロー詳細へドラッグ&ドロップ）します。このアクションは以降のファイル選択等の際に「初期フォルダ」を設定するために変数化しておくものです。

ここでは、特別なフォルダ―の名前： デスクトップ を選択します。特別なフォルダーのパス：は自身のデスクトップのパスが自動設定されます。生成された変数はデフォルトの SpecialFolderPath のままとし、保存します。

【対象フォルダを選択をするためのダイアログ設定】

アクション一覧 → メッセージボックス → ファイルの選択ダイアログを表示 をダブルクリックします。

下図の通り入力し保存します。

ダイアログの説明： 対象のフォルダを選択（任意のテキスト）

初期フォルダ：%SpecialFolderPath% (右端の{X}をクリックし、変数名を選択)

フォルダ―選択ダイアログを常に手前に表示する：オン

生成された変数・ SelectedFolder（初期値）のままとする。

【対象フォルダ内のファイルを取得】

アクション一覧 → フォルダー → フォルダー内のファイルを取得 をダブルクリック。

下図の通り入力し保存します。

フォルダー： %SelectedFolder% (右端の{X}をクリックし、変数名を選択)

ファイルフィルター：*

サブフォルダを含める：オン（オフにしてもかまいません）

生成された変数・　Files（初期値のまま）

【ファイル情報を書き込むエクセルを起動】

アクション一覧 → Excel → Excelの起動 をダブルクリック

下図の通り入力し、保存します。

Excelの起動：空のドキュメントを使用　を選択

インスタンスを表示する：オン

※このあと、「キーの送信」を使用してエクセルを操作するため、ここは必ずオンにしておく必要があります。

生成された変数・ExcelInstance（初期値）

次のアクションについては、一見意味のないように見えるのですが、後述のハイパーリンクをキーの送信で設定するにあたり、これを入れておかないとうまくフローが進まなくなってしまうため、設定するものです。

アクション一覧 → UIオートメーション → ウィンドウにフォーカスする をダブルクリック

下図の通り入力し、保存します。

ウィンドウの検索モード：タイトルやクラスごと 　を選択

ウィンドウタイトル： BOOK1-Excel　を選択

ウィンドウクラス：入力不要

※ここで、ウィンドウタイトルをBOOK1としています。つまり、このフローで開いたExcelファイルはこの時点でBOOK1である必要があります。よって、このフローを実行する際に別ファイルでBOOK1（未保存の新規ブック等）が開かれているとうまく処理ができませんので気をつけてください。

【Excelに見出し行を書き込む】

エクセルファイルの一行目に見出しを書き込みます。今回、書き込むファイルの情報は次の通りです。見出し名は日本語でも構いませんが、わかり易いよう後述のファイルプロパティ名と合わせています。また、ファイルのプロパティで取得できる情報は今回書き出すものがすべてではありませんので必要に応じて追加・変更してください。

　

アクション一覧 → Excel → Excelワークシートに書き込み　をダブルクリック

下図の通り入力し保存します。

Excelインスタンス：　%ExcelInstance%　を選択

書き込む値：　Directory　を入力

書き込みモード：指定したセル上　を選択

列：　1

行：　1

2列目以降はほぼ同様の処理となりますので以下画像の貼り付けのみとします。

【ループ処理用の変数（行数）を設定】

アクション一覧 → 変数 →　{x}変数の設定　をダブルクリック

下図の通り入力し保存します。

設定：　RowCount　とします（任意の値）

宛先：　2　（Excelシートへの書き込みは2行目からである為、初期値2行目とする）

ここまでで作成したフローは以下の通りです。

３．実践②　ループ処理：　ファイル情報の書き込みとハイパーリンク設定

【ループ処理の設定】

アクション一覧 → ループ → For each　をダブルクリック

下図の通り入力し保存します。

反復処理を行う値：%Files%　(右端の{X}をクリックし、変数名を選択)

生成された変数・　CurrentItem（初期値）

【ファイル情報をExcelへ書き込む】

アクション一覧 → Excel → Excelワークシートに書き込み　をダブルクリック

下図の通り入力し保存します。

Excelインスタンス：%ExcelInstance%　(右端の{X}をクリックし、変数名を選択)

書き込む値：%CurrentItem.Directory%　と入力

※変数CurrentItemはFiles変数を格納したものであり、ファイルのデータ型プロパティを持っています。変数のデータ型のプロパティについては公式のリンクを参照願います。

書き込みモード：　指定したセル上　を選択

列：　1

行：　%RowCount%　(右端の{X}をクリックし、変数名を選択)

2列目以降はほぼ同様の処理となりますので以下画像の貼り付けのみとします。

書き込む値のCurrentItemのプロパティに注意してください。

【ハイパーリンク設定に伴うセル選択】

このアクションにより、最初の行のファイル名(Name列）が選択された状態になります。

アクション一覧 → Excel → Excelワークシート内のセルをアクティブ化　をダブルクリック

下図の通り入力し保存します。

Excelインスタンス：%ExcelInstance%

アクティブ化：絶対位置で指定したセル　を選択

列：2

行：%RowCount%

この後「キーの送信」を使ってエクセルを操作する場面がいくつかありますが、通常エクセルで使用するショートカットキーを使います。エクセルのショートカットがある程度頭に入っていた方が理解が早いと思います。

 【ハイパーリンクを設定】

アクション一覧 → マウスとキーボード → キーの送信 をダブルクリック

下図の通り入力し保存します。

送信するテキスト：{Control}({K})　と入力します。

※キーボードのCtrl+K　はエクセルの「ハイパーリンクの挿入」のショートカットキーです。

キー入力の間隔の遅延：100　とします。デフォルトの10だと速すぎてうまく入力ができない場合がある為、100としました。このあたりは、実際にテストしながら調整していけばよいと思います。

テキストをハードウェアキーとして送信します：オフ

【補足】

前述のCtrl+K をエクセルで送信すると下図のダイアログが表示されます。カーソルは「アドレス」の箇所に初期セットされるため、次のPADアクションでは、ファイルパスを送信します。

再度、「キーの送信」アクションから以下の通り入力し保存します。

送信するテキスト：　%CurrentItem.FullName%　

※FullNameプロパティとすることでファイルパスが送信されます。

再度、「キーの送信」アクションから以下の通り入力し保存します。

送信するテキスト：　{Return}

※{Return}= Enterキーとなります。これを送ることで「ハイパーリンクの挿入」ダイアログのOKボタンが押下されたことになります。

【補足】

上述の「キーの送信」アクションでは、3つのアクションを分割して登録しました。1つのアクションでキーを続けて入力することも可能ですが、予期せぬエラー等に対応するためにアクションを分割してひとつひとつの挙動を確認しながら進めるのが無難だと思います。

【RowCount変数(行数)のカウントアップ】

アクション一覧 → 変数 → 変数を大きくする　をダブルクリックします。

下図の通り入力し保存します。

変数名：　%RowCount%　(右端の{X}をクリックし、変数名を選択)

大きくする数値：　1

このループ処理の項目で作成したフローは下図の通りとなります。

４．実践③　キーの送信：エクセルシートの書式調整他

これまでに作成したフローを実行すると、下図のエクセルが出来上がります。想定通りに書き込みはできていますが、列幅が初期のままですので入力値が見えません。また、Sizeが桁区切りでなくわかりにくいため調整します。

【Size(D列）を桁区切りスタイルへ】

アクション一覧 → マウスとキーボード → キーの送信 をダブルクリック

順番として、①D列を全選択し、②D列を桁区切りスタイルに変更したいと思います。この時点で、エクセルシート上のカーソルはB11セル（B列の最終行）にありますので、次のキーの送信で右矢印キー（→）を2回押下し、Clrl + Spaceキー でD列を選択するようにします。

送信するテキスト：　{Right}{Right}{Control}({Space})

次に、Ctrl + Shift + 1　のショートカットで桁区切りスタイルにします。

送信するテキスト： {Control}({Shift}({D1}))

※注意点：「1」とそのまま入力するとうまくいかないため、1のキーを送信するときは {D1} と入力します。

【列幅の自動調整】

①Ctrl+A　でセルを全選択し

②Alt + H 、O、I　で列幅を自動調整します。

【補足】

参考まで、前述の一連のキーの送信をエクセルで操作した場合の挙動を以下画像で貼り付けておきます。

この後、エクセルファイルを保存する前に、アクティブセルをA1にするためにCtrl+Home を入れたいのですが、テストしたところなぜかうまく動作しなかったので遅延処理をはさみました。

アクション一覧 → 遅延 → Wait　をダブルクリック

期間：　1（秒）を入力し保存します。

アクション一覧 → マウスとキーボード → キーの送信 をダブルクリック

下図の通り入力し保存します。

送信するテキスト： {Control}({Home})

５．実践④　エクセル名前を付けて保存：パスからフォルダ名を抽出し付加

 最後に作成したエクセルファイルに名前を付けて保存するのですが、そのエクセルファイル名には対象フォルダの名前を含めたいと思います。フォルダ名単体はプロパティ等からそのままとることはできない為、少し工夫しました。一旦概要を下図にまとめましたので内容を頭に入れたうえでフローの処理に進んでください。

アクション一覧 → テキスト → テキストの解析　をダブルクリック

下図の通り入力し保存します。

解析するテキスト：%SelectedFolder.FullName%(右端の{X}をクリックし、変数名を選択)

検索するテキスト： \

正規表現である：オフ

解析の開始位置：　0

最初の出現箇所のみ：オフ（すべての出現箇所を取得します）

大文字と小文字を区別しない：オフ

生成された変数・　Positions（初期値）

アクション一覧 → 変数 → リストを逆にする をダブルクリック

逆にするリスト： %Positions%　とし保存します。

アクション一覧 → テキスト → サブテキストの取得　をダブルクリック

下図の通り入力し保存します。

元のテキスト：%SelectedFolder.FullName%

※対象フォルダのフォルダパスとなります。ちなみにCurrentItem.FullNameで取得すると最終処理がサブフォルダだった場合にズレが生じてしまいます。

開始インデックス： 文字の位置　を選択

文字の位置： %Positions[0] + 1%　

長さ：　テキストの末尾　を選択

生成された変数・ FolderName　に変更します。

アクション一覧 → Excel → Excelを閉じる　をダブルクリック

下図の通り入力し保存します。

Excelインスタンス：　%ExcelInstance%　を選択

Excelを閉じる前：　名前を付けてドキュメントを保存　を選択

ドキュメント形式：　既定(拡張機能から)　を選択

ドキュメントパス：　%SpecialFolderPath%\%FolderName%_ファイル情報.xlsx

※デスクトップに、対象フォルダ名 + _ファイル情報 の名前で保存する設定にしています。

これでフローの作成は完了です。ループ処理の後から最後までのフローは下図の通りとなります。

６．フローの実行

前項までで作成したフローを保存して一旦閉じ、トップ画面から作成したフローを実行します。

対象のフォルダを選択し、ＯＫをクリックします。

エクセルが起動し、書き込みがされます。下図は書き込み途中の処理です。ちょうど最終行のファイル名にハイパーリンクを挿入しているところです。

正常に処理が完了し、完了の通知が表示されました。

デスクトップにエクセルファイルが作成されています。

エクセルファイルを開き、ハイパーリンクが正しく設定されているか確認するため、試しにリンクをクリックしてみます。

ハイパーリンクの設定通り、pdfファイルが開きました。

７．最後に

思った通りのフローができたのですが、「キーの送信」で操作するとなんとなくチープな感じ（不安定）になってしまう気がします。フロー実行中にキーボードやマウスを操作してしますとうまく実行できなくなってしまうところも気になるところです。