前書き

「マクロは属人化するからこれからはローコード/ノーコードの時代、
PowerQueryにMicrosoft Power AutomateやMicrosoft Power Appsだ!」なんていうもののマクロより情報が散らばるからもっとひどいんじゃないかと思う今日この頃

良し悪しは別にしてExcel関数やマクロを使わずPowerQueryやらを使えと言われたら、それはそれで縛りプレイとしてやる必要があるんですということで今回はPowerQueryの話
実際に作るときにQiitaにはお世話になったのでQiitaで投稿

前提

結合前1, 結合前2は誰かが作って毎月送り込んでくるもので、Excel自体は加工しないものとします。
月が変わると今は10月となっている部分が変わるとします。データの位置などは変わりません。
加工しないということで、PowerQueryを組み込むためのExcelが必要になるため、空の結合用.xlsxを用意しました。

最終的な形は以下の通りとします。
結合前1をベースに、結合前2に書かれた情報を集計してくっつけるようなイメージです。
日付は不要とします。

Excel直接指定(基本形)

Excelを直接指定する場合はデータタブのファイルからを選び、Excelブックからを選びます。

結合前1を選択

10月シートに情報があるので、10月シートを選択して読み込みを選びます。

すると10月というシートができて、データが読み込まれます。

結合2も同様に読み込むと、

日付は整数で出ていますが、今回は関係ないのでスルー

読み込んだ2シートを結合するにはクエリの結合を行います。データタブのデータの取得→クエリの結合→マージを選び、

IDで結合することから、10月のIDと10月売上のIDを選んでOKとします。

すると、PowerQueryエディターで何か出てくるので、

10月売上の右のやれやれみたいな記号を選んでΣ10月金額の合計のみにチェックを入れてOKを押すと、

いい感じになってきます。

名前が変なので新しい列で右クリック、名前の変更で

金額にします

ホームの閉じて読み込むをクリックすると

マージ1というシートができてデータが出てきます。

右の目標と比べてみると、確かに内容は完全に一致しています。めでたしめでたし

めでたくない

10月はこれでいいでしょうが、来月以降に変わる雰囲気を醸し出している要素がたくさんあります。Excelの名前も10月になってるし、シート名も10月っぽいし、10月金額とか書いてある列もあります。もしこれで来月全て11月に変わっていたらどうなるでしょうか
全ての情報を11月に変えてデータタブのすべて更新を押すと

当然エラーが出ます

10月のExcelを指定したので、名前が11月になれば読み込めません

Excel名が変わる場合の対処

Excel名が毎回変わる場合はどうしたらよいかをまず解説
当然毎月名前を変えて送り付けてくるバカを殴るのが一番なのですが、どうにもならない場合は以下のようにします。

今までは1つのフォルダにExcelが3つ並んでいましたが、フォルダの中に、毎月名前の変わるExcelを入れる、名前の変わらないフォルダを作ります。

そしてデータタブからデータの取得を選び、ファイルから、Excelブックからとしていたのを、フォルダーからに切り替えます。

結合前1のExcelを選んでいた代わりに結合前1の入ったフォルダを選択します

すると何か出てきますが、結合のボタンを押して、データの結合と変換とすると、

さっきも出てきたものが出てくるので、11月を選ぶ

何のExcelから読みこんだのかという情報は今回はいらないので削除

閉じて読み込むをクリックするとちゃんと読めます。

これを結合用2でもやっていくと完成です。

シート名が変わるときの対処

Excel名が変わる場合にはフォルダを作ってExcelを直接指定しないことで解決しました。
しかし、11月シートは直接指定していました。じゃあシート名も変わる場合はどうするか。

同様にデータタブのデータの取得、ファイルから、フォルダーからで結合前1を選んで、データの結合と変換を選んで出てきた画面で、シート名ではなく、「パラメーター」と書かれたものを選んでOKとします。

何か不思議な文字がたくさん書かれたPowerQueryエディターが開きますが、Dataの横のマークを選んで、OKとすると

何か多いですが、ちゃんと出てきます。

いらない列を消して、

ホームタブの1行目をヘッダーとして使用を押して保存して読み込むと、何か手数が増えましたが、無事に読み込めます。

結合前2も同様です。

Tips

シートが複数あり、何シート目に情報があるかがわかっている場合は、余計な行を消すとか、

指定のColumnだけを選んでOKなどの方法があります。まあどちらも大した話ではありません。

列名が変わる場合の対処

こんな感じで列の名前も変わる場合にどうするか。

列名が変わると結合前2の型変換とマージで


「1月金額」と書かれているのが問題になります。

なお、後ろの「結合前.1月金額の合計」は累計を表示する行の名前を書いているだけなので適当に変えてもOKです。

さて、結合前1のフォルダを読み込んだ後に

結合前2の1月金額という名前を変えればいいのではと思いもしますが、直接変えると

1月金額という毎月変わる名前が式に入っています。
Table.AggregateTableColumnの「1月金額」をFirst.Nとかで何とかならないか色々試してみたののの、うまくいかなかったので別方向から解決させた結果が以下の通り。

結合前2をなんやかんやで読み込んで、

「1行目をヘッダーとして使用」をすると、変更された型1も出てきて式に1月金額が出てくるので、

これを消す。

そして、列名を指定せずに名前を変える方法を使います。

〇月金額を普通改名すると↓になるところを

↓に書き換えます。

= Table.RenameColumns(昇格されたヘッダー数,{{Table.ColumnNames(昇格されたヘッダー数){3}, "金額"}})

4列目(PowerQuery的には3列目)の名前を固定の「金額」という名前に変えました。

そして先ほど消した金額列の型変換を行います。これをやらないと累計できません。

この後は普通にマージしてOKです。

これがやりたくてこの記事にたどり着いたんだろ?というレベルのお話。

結果

Tips 展開するだけの場合

結合前1のフォルダを読み込むところまで進めておきます。

ただし、後のために結合前2のIDはID2に製作物は製作物2に改名しておきます。

結合して、展開すると以下の通りですが、

この式を

=Table.ExpandTableColumn(ソース, "結合前2", List.Union(List.Transform(ソース[結合前2],each Table.ColumnNames(_))))

に書き換えると列名に依存せず展開できます。

この時に、IDと製作物の名前を変えておかないと重複エラーになります。

まとめ

名前が変わる場合の基本はその上位(Excel名ならフォルダ名、シート名ならパラメーター)を指定する
列名が変わる場合は列番号から固定列名に変更する

後書き

で、これならローコードだからマクロと違ってユーザーだけでなんとかなるんですよね?