タグ別アーカイブ: 変更された型

Power Queryのステップ数を減らす3つの工夫

 Power Queryの便利な点の一つは、「適用したステップ欄」に操作履歴が自動記録される点です

 ただ、後々のクエリの操作性を考えると「ステップ数」は少ない方がいいですよね?

今回は「ステップ数」を減らす工夫を3つ紹介します!

「変更された型」のステップを減らす

 こちらは、過去の記事でも同じような事を書いていますので、ぜひそちらもご参照ください

 Power Queryでは列のデータ型とヘッダーの自動検出により、「ソース」ステップの次に、必ず「変更された型」のステップが追加されます

 後、途中でステップを追加した際、下のGIF画像のように自動的に「変更された型」が自動追加されることもあります

 一方で、変換タブにはきちんと下の画像のように「変更された型/列のデータ型とヘッダーの自動検出」のステップを追加する機能があります

ですので、変更された型ステップは「自動追加」する必要は必ずしもありません

 クエリを読み込む前、もしくは必要になった箇所に「手動追加」すれば変更された型ステップは減らすことができます

 変更された型ステップの自動追加を止める処理は、エディタ内の下の画像の箇所から行います

 ファイルタブ➡オプションと設定 とクリックすると「クエリのオプション」がクリックできるようになります

 クエリのオプションが開いたら、下の画像のように「型の検出」から3つの選択行えるようになっています

 一番下の「非構造化ソースの列の型とヘッダーを検出しない」にチェックを入れると「変更された型」ステップの自動追加は行われないようになります

同じ処理をまとめて行う

仮に、下の画像のようなデータがあったとします

このデータ内で次のように4つの処理を行ったとします

①フィルター:部門の列で「東京」のみを選択

②列の名前変更:部門➡東京

③フィルター:受注金額の列で「150万以上」のみを選択

④列の名前変更:受注金額➡150万以上

すると、次の画像のように4つのステップが①~④の処理に応じて追加されます

4つのステップが追加された結果、エディタ内は次の画像のようになっています

では、前述の①~④の処理の順番を次のように変えてみます

1.フィルター:①と③

2.列の名前の変更:②と④

すると、ステップは4つでなく2つとなります

 このように、同じ種類の処理をまとめて行えば、ステップ数は減らすことができます

列名の変更

 エディタ内で処理を行った際に、自動的に名前が変更、もしくは追加されている時があります

 上のGIF画像では、列の分割処理を行った際に「部門.1」と「部門.2」という名前が自動的についています

この場合、数式バー内では新たに追加された「列名」は赤字になっています

 では、この追加された「部門.1」「部門.2」の列名を下の画像のようにそれぞれ「」「」に変更してみます

 すると、下の画像のように「名前が変更された列」が適用したステップ欄に追加されています

 このステップについては、数式バーで直接、前述赤字 部分を変更することで減らすことができます

 数式バーで名前を直接・変更すれば、下の画像のように「位置によって分割された列」のステップの後のステップは消えています

<まとめ>

今回は、ステップを減らす為の工夫を3つ解説しました

 特に、最初に紹介した「変更された型」のステップを減らすについては、すぐに削減効果が出ると思います

 後に紹介した2つの削減工夫については、「効率的なクエリ作成」を意識していけば、自ずと「ステップ数の削減」につながるものです

今回の記事を機に、「効率的なクエリ作成」をぜひ意識してみてください

長文に最後までお付き合い頂き誠にありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

 

エラー発生の予防/列のデータ形式変更と削除について~上級19回~

 Power Queryの便利な点は、普段から使い慣れたエクセルをAccessのようなデータベースとして活用できる点です。このページを訪問して頂いた方の中にもAccessは細かなルールが多くて不便と感じた方もいらっしゃったと思います。

 Power Queryは自動で、Accessの不便な点を補ってくれる機能があります。そのかわり、エラーが出やすい箇所があります

このエラーはPower Queryの2つの特徴と深く結びついています

 Power Queryの2つの特徴について解説しながら、「列名変更」に関するエラーの発生を防ぐ方法について解説します

 エラーの発生を防ぐ方法を理解した時には、「データ形式」も含めてPower Queryへの理解が一段と深まっているはずです!

ところで、

エクセルとAccessの違いとは何でしょう??

エクセルはあくまで表計算ソフトです

Accessはデータベースソフトです

 この2つの違いを、別な言い方で表現すると「データの構造化」ということになります

こちらが、Power Queryの特徴の1つ目の話しです

Accessでは、いきなりデータから入力はできないようになっています

 上の画像のように「データを入力する箱」をフィールド毎(エクセルでは列毎)に「フィールド名/見出し」「データ型」を設定しなくてはいけません

つまり、予め入力するデータの箱を「構造化」しておくわけです

 今回の解説では、上の画像についてはこれ以上は深入りしませんが、上で前述した「データの構造化」を意識して、以降の記事を読み進めてみてください!!

今回、解説に使用するデータは次の画像のデータです

 こちらの3列(売上日、商品コード、販売個数)からなるテーブルデータから、下の画像のように商品コードの1列を抽出するクエリを作成しておきます

そして、元のテーブルデータの「販売個数」の列名を変えます

すると、「商品コード」のみを抽出するクエリを更新するとエラーになります

 エラーの中味を見てみると、抽出する「商品コード」とは関係ない列の「名前変更」によりエラーが発生しています

実は、このエラーは前述の「データの構造化」が深く関係しています

では、こちらのエラーを回避する方法を以下、2パターンで解説します

1.ステップ「変更された型」の削除

まずは、前述のクエリの中味をPower Queryエディタ(以降、エディタ)で見てみます

適用したステップは3つあります

こちらの3つのステップを、上から順に各ステップの「数式」を見てみます

①ソース

こちらは、ファイル内の「売上台帳」テーブルをデータソースとして読み込んでいるのが分かります

②変更された型

こちらのステップでエラーが発生しているのが、よく分かります

こちらの数式に含まれる「販売個数」は、既に名前が変更されているのでエラーが発生しています

③削除された他の列

こちらのステップで「商品コード」列のみを抽出しています

 実際には、次の画像のように「商品コード」以外の列を削除してステップが作成されています

①~③のステップの中味を確認したところで、エラー原因となった②のステップを削除してみます

すると、エラーは消えます

仮に元の列名が「販売個数」の列名を、再度変更してもエラーは発生しません

これで、列名の変更でエラーが発生しないクエリに変更できました

ところで、

今回のエラー発生の原因となった「変更された型」のステップとはなんでしょうか?

エディタを開く時には、「変更された型」のステップは自動挿入されています

このステップは記事の冒頭で前述したPower Queryの1つ目の特徴である「データの構造化」と深くかかわっています

試しに下の画像のように、最初からクエリを作成してみます

開いたエディの中味を見ると、「変更された型」が前述のように自動追加されています

そして、各列も自動で「型式」が変更されています

例えば、商品コードの型式は元々は「文字列」でしたが、下の図のように「123」マークの「整数型式」に自動変換されています

下の図のように元のデータにて、数字かどうかを判定する数式「ISNUMBER」で判定するとFALSEになり、「文字列」だったことがよく分かります

記事の冒頭で前述したように、エクセルはあくまで表計算ソフトです

表計算ソフト内にあるデータは、データベースとしては構造化されていません

Power Queryでは、Accessとは違い、自動でエディタ内にて「構造化」の設定を行ってくれているのです

ちなみに、AccessのファイルをPower Queryで読み込むと「変更された型」のステップは発生しません

 AccessファイルをPower Queryで読み込む方法は過去の記事で紹介していますので、興味のある方は試してみてください

話をPower Queryの「構造化」に戻します

「構造化」は元データの全ての列に対して設定を行うので、エディタ内で削除した列も設定の対象です

しかも「構造化」は元の列名に対して行われます

ですので、一見、クエリに関係ない列の「名前の変更」が影響するのです

 但し、「商品コード」が文字列から「整数」に変換されたように、常に正しく「構造化」が行われるわけではないので注意が必要です

自動で変更された型を変更する場合には、下のGIF画像の箇所で調整を行います

下の図のように「文字列」は「テキスト」となっている点に注意してください

上の図の詳細な内容については、一覧でMicrosoft社のHP内で紹介されています

さて、

 エラー発生の対策をしたクエリからは「変更された型」のステップは削除されていますが、こちらのステップを後から追加することもできます

上の画像の黄色の箇所にある、変換タブ内の「データ型の検出」をクリックすると、「変更された型」のステップが追加されます

2.削除の仕方の変更

2.削除の仕方の変更では、1.とは別なアプローチでエラーを解決してみます

まず、エディタ内の画面左側から1.でエラーが発生しなくなったクエリを「複製」してみます

複製したクエリの最終ステップでは、前述のように「商品コード」以外の列を削除しています

画像に alt 属性が指定されていません。ファイル名: image-235.png

こちらのステップを削除して、次のGIF画像のように他の2つの列を1つ1つ削除します

2つの列を削除した後、エクセルシートに読込みます

次に、列名を「TEST2」から「TEST3」に変更すると複製したクエリはエラーになります

このエラーの原因は「列の削除の仕方」にあります

下の図のように、列名変更前の「TEST2」の列名を指定して「列の削除」をおこなっているからです

 こちらのエラーについては下の画像の黄色の箇所のように、列の削除の仕方を「列名」を指定しない方法(他の列の削除)で行えば発生しません

画像に alt 属性が指定されていません。ファイル名: image-235.png

このエラーはPower Queryの「ハードコード」という特徴と深く結びついています

Power Queryでは、エディタ内の操作がステップとして記録されます

このステップ内には、も記録されます

前述のエラーで言えば、「列名を変更する前」の列名です

こちらは、列名が変更になると「ハードコード」した列名と一致しなくなってしまい、エラーになったのです

<まとめ>

 今回は、エディタ内で削除した「列名」を後から修正した場合に発生するエラーの修正方法について2つ解説しました

 1つ目は「変更された型」という自動で追加されるステップを削除してエラーを回避できるようにしました

2つ目は列の削除の仕方を、削除する列名を特定しない方式に変更しました

2つの方法はそれぞれ、Power Queryの2つの特徴と深く結びついています

 1つ目の「変更された型」に関するエラーは「データの構造化」というPower Queryの特徴と結びついています

 エディタでデータを読み込む際に、全ての列を対象にして「見出し」「データ形式」を設定します

ですので、エディタ内で削除した列の「列名変更」がエラーの原因になります

 こちらの「変更された型」については、「変更された型」のステップを削除する方法ともう一つ、対処方法があります

データタブの「データの取得」をクリックすると一番下に「クエリオプション」が出てきます

こちらをクリックすると次の画像の画面が開きます

こちらの画面で、「非構造化ソースの列と型とヘッダーを検出しない」を指定しておくという方法もあります(自動で検出されていた部分が検出されなくなるので注意が必要です)

ここまでで、Power Queryの1つ目の特徴についての「まとめ」を解説しました

次は2つ目の特徴である「ハードコード」についてです

Power Queryではエディタ内の操作が「ステップ」として記録されます

こちらの「ステップ」には値も直接書き込まれます

ですので、前述の2.で列を削除した際には「列名」も値として記録されました

ですので、エディタ内で削除した列の「列名」が変更になると、列名の「不一致」が発生してエラーになったのです

下の画像で言えば、「TEST2」の列名の列を削除するように数式が設定されていましたが、既に「TEST3」に列名が変更になっていたのです

画像に alt 属性が指定されていません。ファイル名: image-248.png

 今回はPower Queryの2つの特徴について、理解を深める機会になったと思います

この2つの特徴を理解しておくと、Power Query自体への理解も深まります

 ぜひ今回を機会に「データの構造化」「ハードコード」について意識した上でPower Queryに取り組んでいきましょう!

では、今回は以上となります

参考までに今回使用したエクセルデータを添付します

アイコン

上級19回 24.18 KB 10 downloads

...

長文に最後までお付き合い頂き、まことにありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村