11回目では、複数のエクセルファイルをまとめる方法を説明しました。今回はCSVファイルのまとめ方で、なおかつファイルによって列名の書き方が違っている場合でもうまくいくようにします。
列名が違っているものが混じってる時、１つ１つ直してからまとめる作業をやっていませんか？違いにパターンがある場合、変換テーブルを作成して、列名を自動で変更しながらまとめあげます。
パラメータの設定
フォルダーの位置をパラメータに設定します。
列名変更ルールの作成
変更前、変更後のテーブルを作成します。
「データの入力」で作成すれば簡単に作成できます。
Power Queryのコードで書く場合は、以下のように書きます。
let
Source = Table.FromRecords(
{
[BEFORE="Segment", AFTER="区分名"],
[BEFORE="Country", AFTER="国名"],
[BEFORE="Product", AFTER="製品名"],
[BEFORE="Discount Band", AFTER="割引幅"],
[BEFORE="Units Sold", AFTER="販売数"],
[BEFORE="Manufacturing Price", AFTER="原価"],
[BEFORE="Sale Price", AFTER="単価"],
[BEFORE="Sales Price", AFTER="単価"],
[BEFORE="Gross Sales", AFTER="総売上高"],
[BEFORE="Discounts", AFTER="割引"],
[BEFORE=" Sales", AFTER="売上高"],
[BEFORE="Sales", AFTER="売上高"],
[BEFORE="COGS", AFTER="売上原価"],
[BEFORE="Profit", AFTER="利益"]
},
type table[
BEFORE = Text.Type,
AFTER = Text.Type
]
)
in
Source
今回は、列名をすべて日本語に変換するほか、"Sale Price"は"Sales Price"となっている場合があり、"Sales"は、頭に空白が入ってることがあるため、いずれもまとめる際に１つの列に入るようにします。
ソースコード
全体のコードは、以下のようになります。
let
Source = Folder.Files(FolderPath),
// CSVデーターソースにアクセスし、１行目をヘッダーに設定
Step1 = Table.TransformColumns(
Source,
{
"Content",
each
Table.PromoteHeaders(
Csv.Document(_, [Encoding=932]) // Shift_JIS
)
}
),
// マッピングテーブルを参照して列名を変換
Step2 = Table.TransformColumns(
Step1,
{
"Content",
each
Table.RenameColumns(
_,
Table.ToRows(Mapping),
MissingField.Ignore
)
}
),
// データテーブルを結合
Step3 = Table.Combine(
Step2[Content]
)
in
Step3
CSVファイルを読み込む
Sourceにフォルダー内のファイルを読み込みます。
フォルダーの中に、まとめるCSVファイル以外のものが混じっている場合は、１ステップ追加して、Name列かExtension列でフィルターをかけます。
読み込んだ状態では、上記のように、ファイルの中身はBinaryになっているので、Step1でこれをCSVと認識させ、１行目を列名にします。
今回のファイルは、漢字コードがShift_JISになっていたため、Encoding=932を付けています。UTF-8の場合は、Csv.Document(_)で読み込めます。
列名の変換
Table.RenameColumnsを使う
Table.RenameColumnsを使用して、列名を変更します。
最初に、Table.TransformColumnsで、Content列にあるテーブルを１つ１つ処理(each)するようにします。
そのなかで、Table.RenameColumnsを使いますが、第２引数には、列名が新・旧のリスト形式になっている必要がありますが、最初作った変換テーブルは、テーブル形式です。これを、Table.ToRowsを使ってリスト形式にしています。第３引数のMissingField.Ignoreは、当てはまる列名がなくてもエラーにならないようにするオプションです。
Table.TransformColumnNamesとList.Accumulateを使う
List.Accumulateを使って列名を変換するやり方がYoutubeにありました。
そのやり方でStep2を書き換えると以下のようになります。
このやり方は、Table.RenameColumnsを使うより複雑です。
// マッピングテーブルを参照して列名を変換
// How to Merge Excel Files with Different Headers in Power Query | List.Accumulate
// https://www.youtube.com/watch?v=wKglApDFMog
Step2 = Table.TransformColumns(
Step1,
{
"Content",
(_Table) => Table.TransformColumnNames(
_Table,
(_ColumnName)=> List.Accumulate(
Table.ToRecords(Mapping),
_ColumnName,
(state, current) =>
Text.Replace(
state,
current[BEFORE],
current[AFTER]
)
)
)
}
),
最初に、Table.TransformColumnsでContent列のテーブルをTable.TransformColumnNamesに渡します。
次に、Table.TransformColumnNamesは、現在の列名の変換先をList.Accumulateで探します。
List.Accumulateは、現在の列名を変換テーブルのBEFOREを順番に当たって、見つかったらAFTERの値を返します。見つからない場合は、現在の列名が返ります。つまり、変更されません。
accumulateとは蓄積する、累算するという意味で、state、currentを使って処理が積み重なっていきます。最初にシード値として現在の列名が与えられ、変換テーブルのBEFOREに一致する値があった場合、AFTERに置き換えられます。
注意が必要なのは、Text.Replaceで行われるのは、部分一致だということです。例えば、変換テーブルに [BEFORE="Sale", AFTER="売"] のようなレコードが入っていた場合、正しく動作しません。
そこで、以下のように列名の比較部分を書き換えて、完全一致で変換するようにします。
Step2 = Table.TransformColumns(
Step1,
{
"Content",
(_Table) => Table.TransformColumnNames(
_Table,
(_ColumnName)=> List.Accumulate(
Table.ToRecords(Mapping),
_ColumnName,
(state, current) =>
if state = current[BEFORE] then
current[AFTER]
else
state
)
)
}
),
表を結合する
CSVの中身だけを結合する
一番簡単な方法で、Table.Combineを使って列を指定してやれば、一気に結合することができます。最初のコードは、このやり方です。
ファイル名も残して結合する１
各テーブルの列名を取り出してリストを作成し、Table.ExpandColumnで展開します。
// データテーブルを結合
Step3 = Table.ExpandTableColumn(
Step2,
"Content",
List.Union(
List.Transform(
Step2[Content],
each Table.ColumnNames(_)
)
)
)
現在の表はそのままで、表の頭にテーブルのデータが展開されます。
ファイル名も残して結合する２
CurbalのYoutubeで紹介されている、CSVテーブルの中にファイル名の項目を追加する方法です。
// データテーブルにファイル名の列を追加
Step3 = Table.AddColumn(
Step2,
"NewContent",
each
Table.AddColumn(
[Content],
"File",
(x) => [Name]
)
),
// データテーブルを結合
Step4 = Table.Combine(
Step3[NewContent]
)
Content にファイル名の列を追加した、新しい列 NewContent を作成しています。これを、Table.Combineで結合すれば、ファイル名の入ったテーブルが出来上がります。ファイル名の列は、後ろの方につきます。
位置は、最初のテーブルの最後に追加されますが、他のテーブルで項目が増えた場合、その項目が後ろに追加されるため、必ずしも位置が最後とはなりません。
また、この式の説明は #11 で行っていますので、そちらを参照してください。
