先日、こんなツイートをしました。
今回は、この中でまだ解決できていない弱点があったのです。
そう、列数が変わるデータを取り込みたいときの対応策！
この問題について書いていこうと思います。
ちなみに解決できている項目はこちら。
今回取り扱うデータ
今回取り扱うデータはこの2種類のcsvデータです。
この2種類の給与支給データを、パワークエリのデータソースを切り替えるだけで取り込めるようにしたいというのが今回の趣旨です。
ちなみにデータの形式として、A列の項目列は不変、B列以降の支給人員が月によって変動があるというのを想定しています。
データの取得
まずはExcelのデータタブからデータを取得します。
今回はcsvファイルを取り込むので、「テキストまたはCSVから」を選択します。
データの変換を選択し、
まずは「給与支給データ2.csv」の取り込みが完了しました。
列数指定の取消し
csvデータを取り込んだ場合、読込データテーブルの列数が指定されていますので、それを取り消すことで列数指定を外すことができます。
これは「詳細エディター」からクエリを直接編集します。
詳細エディターを押して、直接編集画面を開きます。
ここで「Columns=3」とあるのが列数を指定しているところです。
これを「Columns=null」とすることで列数指定を外します。
変更後のエディター画面がこちらです。
このように、「Columns=null」となっていればOKです。
ちなみに、Excelファイルを取り込む場合は下の図の通りで列数指定が無いので、この処理は不要です。
では、次のステップに移りましょう。
「変更された型」の削除
次に、画面右の適用したステップから「変更された型」を削除します。
なぜかというと、列数が変わった時にこのステップでエラーになるからです。
エディター画面を見ればそれが分かります。
このように、各列が指定されているので、列数とタイトルが変動するとこのステップで、このエラーが起きます。
- 指定されていない列は型が変更されない。
- 指定列が無い場合はエラーになる。
ここまでで、こんな画面になります。
では、次に「給与支給データ16.csv」を取り込んでみましょう。
列数が違うデータの取り込み
適用したステップ「ソース」横の歯車をクリックし、ファイルパスに次に取り込むデータを指定します。今回の場合「給与支給データ16.csv」です。
するとこのように列数が変わっても問題なく取り込むことができました。
ところで、この金額のデータ型がまだテキストのままなので、ここから集計等の加工する場合にはデータ型を数値に変更しないといけません。
次はその方法について見ていきます。
ピボット解除とデータ型の変更
データ型の変更を列ごとにやっても、次に別ファイルを取り込むときにエラーになるのは先ほど説明した通りです。
ではどうするかというと、ピボット解除によってクロス集計列からデータベース形式に変換してデータ型を変更すればOKです。
ピボット解除した後にデータ型を数値に変換します。
こうすることで、取り込む列数や列タイトルが変わったとしてもピボット解除後のテーブルは列タイトル・列数どちらも固定されていますので、列数・タイトルが変わる問題を回避することができます。
ここまでの処理をエディターを確認するとこのようになります。
列数や列タイトルが変わった時に影響を受ける項目が無いことを確認しましょう。
この中でA列・B列・・・といった項目は出てきませんので、取込データの列数やタイトルの影響を排除することができていることになります。
まとめ
本日のまとめです。
- 詳細エディターで列数をnullにする。
- ピボット解除してからデータ型を変更する。
- 最後に詳細エディターで変動がある列タイトルが含まれてないことを確認する。
以上、列数・列タイトルが変わるデータを取り込む場合の処理方法をまとめてみました。
最後までお読みいただきありがとうございました！
