【パワークエリ】各列のデータ型をワークシートを経由して一括で変更する方法
パワークエリでデータ型をぽちぽち変更するのが面倒だと思っており、別の方法を考えてみました。
【目次】
１．やりたいこと
パワークエリでは基のデータをエディターに取り込んだ後、各列のデータ情報から自動的にデータ型を変更してくれます。その結果は、クエリの設定ペインで”変更された型”のステップとして記録されます。この自動で判断してくれる型がすべてこちらの思惑通りの型となっていれば問題ないのですが、必ずしもそうはなりません。例えば、次の画像の「会計年度」列は整数型とて自動変換されていますが、本当はテキスト型としたいのです。
そのような場合、通常は次の画像のように、列見出しの左をクリックし、変更したい型を選択 → 列タイプの変更で現在のものを置換（又は、新規手順の追加）をクリックすることで変更することができます。これが、一つや二つであればよいのですが、多数の列の型を変更する場合、非常にストレスを感じます。（いちいち列タイプの変更も聞かれますし）
そこで、エクエルのワークシート上で次の画像のようなデータ型の変更用シートを作成して、変更した結果(B2セル）をパワークエリエディター上の数式バーに貼り付け一括変更する方法を考えてみました。このシートの薄い青色セルにはテキストを入力し、薄い黄色セルにはあらかじめ数式を入力しています。
下の画像は数式を表示したものです。詳細は後で説明します。
２．クエリをワークシートへ 読み込み～変換用シートの準備
まず、パワークエリエディターで取り込んだ基データをワークシートへ読み込みます。
今回ワークシートへ読み込んだデータは「テーブル2」となっています。
同じブックに新規ワークシートを挿入し、次の画像のようなフォーマットを作成します。シート名は何でもよいですが、今回は「型変換用」としました。また、B1～F1セル、B2～F2セルは後で見やすいようにそれぞれセルを結合しています。
次に再度エディターを起動し、自動生成されている「変更された型」ステップの数式バーの値を全選択し、コピーします。コピーしたら保存せずにエディターを閉じます。
ワークシートに戻り、B1セルに先ほどコピーしたエディターの数式を貼り付けます。ここはテキストとして貼り付けますので、最初に ’ を入力してから貼り付けするようにします。次に、C4セルに今回対象とするテーブル名 テーブル2 を入力します。
３．変換用ワークシートの数式と各項目の説明
ここからは変換用ワークシートの数式と各項目の説明をしていきます。
まず、B7セルの「テーブル列」です。ここには今回対象となるテーブル2の列名を列方向に表示します。
使用する関数
|
検索/行列: テキスト値によって指定されるセル参照を返します。 INDIRECT(参照文字列, 参照形式) |
|
検索/行列: 指定したセル範囲のデータの行と列を入れ替えた配列を返します。 TRANSPOSE(配列) |
B7の数式：
=TRANSPOSE(INDIRECT($C$4&"[#見出し]"))
B7の数式の説明：
INDIRECT関数でC4に入力した「テーブル2」と [#見出し]を合わせたテキスト「テーブル2[#見出し]」を参照してテーブル2の列名を取得。更にこれを列方向に配列表示するためにTRANSPOSE関数で囲います。
※上記数式はC4を絶対参照にしていますが、相対参照のままで問題ありません。
テーブル2の列名が列方向に表示されました。
次はC7セルの「参照（変更前）」列です。この列は変更するデータ型を判断するための参考としてテーブル列に対するテーブル2の1行目を表示するものです。（無くても問題はありません）
使用する関数
|
検索/行列: セル参照または配列から、指定された位置の値を返します。 INDEX(配列, 行番号, 列番号) |
|
検索/行列: テキスト値によって指定されるセル参照を返します。 INDIRECT(参照文字列, 参照形式) |
|
検索/行列: 参照セルまたは配列で値を検索します。 MATCH(検索値, 検査範囲, 照合の種類) |
C7の数式：
=INDEX(INDIRECT($C$4),1,MATCH(B7,INDIRECT($C$4&"[#見出し]"),0))
C7の数式の説明：
INDEX関数でテーブル列(売上番号)に対するテーブル2の1行目の値を取得します。INDEX関数の第1引数：配列はテーブル2とするため、INDEX関数でC4セルを参照します。第2引数：行番号は1行目ですので1とします。第3引数：列番号はMATCH関数を用いて第1引数：検査値（B7=売上番号）が、第2引数：検査範囲（テーブル2の見出し）のどの位置に表れるかとし、第4引数：照合の型は0の一致するもとします。
※C7の数式はこの後下行へコピーしますので数式中のC4は絶対参照とします。
C7にテーブル2の売上番号列の1行目の値が表示されました。C7をコピーし下行へ貼り付けます。
次は、D7セルの「現在のデータ型」です。
ここは変更前としてエディターの数式を貼り付けたB1セルの値から、テーブル列に対応する変更前のデータ型を表示させます。
使用する関数
|
テキスト: 指定した文字列の開始位置から特定の文字数を返します。 MID(文字列,開始位置,文字数) |
|
テキスト: 指定された文字列を他の文字列の中で検索し、その文字列が最初に現れる位置を左端から数え、その番号を返します。 FIND(検索文字列, 対象, 開始位置) |
D7の数式：
=MID($B$1,(FIND(",",$B$1,FIND(B7,$B$1,1))+2),( (FIND("}",$B$1,FIND(B7,$B$1,1) )-1)-(FIND(",",$B$1,FIND(B7,$B$1,1))+1) ))
D7の数式の説明：
MID関数でB1セルの中からテーブル列(売上番号)に対するデータ型の位置と文字数を特定し抜き出します。開始位置と文字数はそれぞれFIND関数を用いて取得します。
MID関数の開始位置(第2引数)の説明： (FIND(",",$B$1,FIND(B7,$B$1,1))+2)
特定したいのはＢ1セルの売上番号の直後に出てくるデータ型（Int64.Type）の「I」の位置です。そのためにまず、文字列「売上番号」の位置を取得します。それが2番目に出てくる FIND(B7,$B$1,1)の部分です。この計算結果は37となります。そしてこれを1番目のFIND関数の第3引数(開始位置）とします。
つまり、FIND(",",$B$1,37) となり、B1セルの37番目の文字（売）以降で最初に",”が出てくる位置を特定します。それが、"Int64.Type"の直前の","の位置で、計算結果は42となります。最後に+2をしているのは実は”,”と"Int64.Type"の間に" ”（半角スペース）が入っている為です。（エディターからコピーしてきた際に余計な半角スペースが多数入っています。）
MID関数の文字数(第3引数)の説明：
’( (FIND("}",$B$1,FIND(B7,$B$1,1) )-1)-(FIND(",",$B$1,FIND(B7,$B$1,1) )+1))
特定したいのはＢ1セルの売上番号の直後に出てくるデータ型（Int64.Type）の文字数（10文字）です。ここは更に2つに分けて説明します。
①まず、”Int64.Type”の最後の文字"e”の位置を特定します。これが最初の FIND("}",$B$1,FIND(B7,$B$1,1))-1 の部分で先ほどと同様にFINDの第3引数(開始位置)はFIND(B7,$B$1,1)＝売上番号の位置（37）とします。Ｂ1セルの37文字目以降で最初に"}”が出てくる位置 54文字目を特定し、この1文字前が"e"となりますので、-1をします。つまり、53 となります。
②次は”Int64.Type”の最初の文字"I”のひとつ前の文字位置の特定ですが、これは第2引数(開始位置)と同様のFIND(",",$B$1,FIND(B7,$B$1,1)) に+1を足したもの、つまり43 となります。
そして①-②を引いた、53-43 がInt64.Type の文字数となるため、最初の数式となるわけです。
長くなりましたこれでMID関数の説明は終わります。
※D7の数式はこの後下行へコピーしますので数式中のB1は絶対参照とします。
D7にテーブル列(売上番号)の現在のデータ型が表示されました。D7をコピーし下行へ貼り付けます。
次は、E7セルの「変更後セット」です。
ここは「変更後のデータ型」(E列）を入力した後に、変更後のエディターに貼り付ける数式(B2セル）を作成する際に参照するセル(列)となります。「変更後のデータ型」がブランクでない(入力されている)場合は「変更後のデータ型」が入力され、「変更後のデータ型」がブランク(入力されていない)の場合は「現在のデータ型」が入力されるような数式になっています。
使用する関数
|
論理: 値または数式が条件を満たしているかどうかを判定します。 IF(論理式, 真の場合, 偽の場合) |
F7の数式：
=IF(E7<>"","{"&""""&B7&""""&","&E7&"}","{"&""""&B7&""""&","&D7&"}")
F7の数式の説明：
E7(変更後のデータ型)がブランクではない場合、
{” ＆B7(テーブル列) & ”, &E7(変更後のデータ型)& }
E7(変更後のデータ型)がブランクの場合は、
{” ＆B7(テーブル列) & ”, &E7(現在のデータ型)& }
となります。
この時点ではE7はブランクですので、後者の結果{"売上番号",type text}
が表示されました。E7をコピーし下行へ貼り付けます。
最後は、B2セルの「変更後」のエディタへ貼り付ける数式を作成するための数式です。
使用する関数
|
テキスト: 指定した文字列の開始位置から特定の文字数を返します。 MID(文字列,開始位置,文字数) |
|
テキスト: 指定された文字列を他の文字列の中で検索し、その文字列が最初に現れる位置を左端から数え、その番号を返します。 FIND(検索文字列, 対象, 開始位置) |
|
テキスト: 複数の範囲や文字列からのテキストを結合し、結合する各テキスト値の間に、指定した区切り記号を挿入します。 区切り記号が空の文字列の場合は、範囲が連結されます。 TEXTJOIN(区切り記号, 空の文字列を無視, 文字列1, 文字列2, ..., 文字列252) |
B2の数式：
=MID(B1,1,FIND("{",B1,1))&TEXTJOIN(",",1,F7:F100)&"})"
B2の数式の説明：
①まず、MID関数でB1セルの最初の文字列からB1セルの中で最初"{”が表示れる位置までのテキストを抜き出します。よって、”= Table.TransformColumnTypes(ソース,{”が抜き出されます。
②TEXTJOIN関数で、第3引数：F7セルからF100セル（100までにしていますが必要に応じて増やしてください）までに入力されたセルを第1引数：区切り記号”,”で結合します。第2引数の”空の文字を無視”は1（TRUE）としていますので、F7からF100の間のブランクセルは無視されます。0（FALSE）としてしまうとブランクセルが反映され余計な","がブランクセル分残ってしまうので注意してください。
①＆②＆"})”とします。
これで、B2セルに変更後の数式が入力されました。この時点では、変更後のデータ型は何も入力されていませんので、変更前と変更後は同じ状態になるのですが、最初にエディターからコピー貼り付けした変更前(Ｂ1セル）の数式には余計な半角スペースが入ってしまっている為、次の画像の変更前と変更後は若干数式の長さが変わっています。ただ、この半角スペースがなくても問題はありませんので気にしなくて大丈夫です。
ここまでで、変換用ワークシートの作成が完成です。次の項目で実際に変更後のデータ型を入力していきます。
４．変更後の数式の作成～パワークエリエディターへの貼り付け検証
作成した変換用ワークシートに変更するデータ型を入力していきます。
次の画像のように「変更後のデータ型」を入力するセルには予め、データの入力規則にてデータ型をリスト化して誤ったテキストを入力できないようにしています。データの入力規則の説明は割愛します。
ここではテーブル列（売上番号)の現在のデータ型（整数型：Int64.Type）をテキスト型：type text に変更します。
すると、E7セルとB2セルの値も正しく変更されました。
同様に他の変更する項目についても変更後のデータ型に入力します。
すべての変更が完了したら、Ｂ2セルを選択し変更後の数式をコピーします。関数自体をそのままコピーして大丈夫です。
そして、パワークエリエディターを起動して変更された型ステップの数式バーの値を削除し、先ほどコピーした数式を貼り付けます。
すると、ワークシート上で変更した項目が正しく変更されました。（次画像で表示されていない項目についても正しく変更されています。）
５．さいごに
実際にこの変換用ワークシートが役に立つかどうかはケースによると思います。そもそものデータの列数が少なく、修正する型の数も少なければクリックで個別に直す方がはやいと思いますし、エディターの数式を直接手書きで直してもよいと思います。ただし、データの列数、修正する型の数が多くなればなるほど手で直すのは容易ではありませんのでそのようなケースではこの方法は使えるのではないでしょうか。また、一度この変換用ワークシートを作っておけば別のデータにも流用することが可能です。ここまでかなり長くなってしまいましたが、最後まで読んでいただきありがとうございました。
