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

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

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

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

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

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

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

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

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

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

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

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

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

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

同じ処理をまとめて行う

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

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

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

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

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

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

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

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

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

１．フィルター：①と③

２．列の名前の変更：②と④

すると、ステップは４つでなく２つとなります

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

列名の変更

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

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

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

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

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

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

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

＜まとめ＞

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

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

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

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

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

にほんブログ村

　

　これまで何度か縦横並びを縦縦並びに変える「ピボット解除」については解説を行ってきました

　今回は、逆に縦縦並びのデータを「縦横」並びに変える「列のピボット」について解説します

　今回解説する「列のピボット」は、「集計」というステップも必要な場合には、相当便利な処理になります！

解説に使用する「元データ」は次の画像のデータです

こちらのデータを「地域」を軸にして、横展開します

実は、元データには「重複」データが含まれていますので、横展開する際には「合計」処理も必要になります

１．Power Queryエディタを開く

元データの上で右クリックし、Power Queryエディタ（以降、エディタ）を開きます

２．ピボットする列を選択

今回は、記事の冒頭で紹介したように「地域」を横展開します

３．「列のピボット」を実行

「列のピボット」はピボット解除のように右クリックではなく、「変換タブ」の「列のピボット」から指定します

上の画像の「列のピボット」をクリックすると次の画像が開きます

要は表の中味となるものを指定するわけですが、こちらは「売上金額」になります

値を指定して「OKボタン」を押すと次の画像のように、記事の冒頭で紹介した重複データも「合計」され、「列のピボット」が完成しています

４．合計以外の計算

前述の３．では「合計」処理を行いましたが、「平均」や「カウント」も計算できます

「列のピボット」ダイアログで「詳細設定オプション」の左横にある▼マークをクリックすると「合計」以外の方法も指定できます

平均を選択すると次の画像のように表示されます

もし、エクセルシートに読込んだ際に、エラーが発生した場合には、自動追加された「変更された型」ステップが不適切に行われている可能性があります

その場合には、上の画像の「変更された型」を削除するか、データ形式を変更するなどの処理が必要になります

今回の解説は以上です

最後まで記事を読んで下さり、誠にありがとうございました

にほんブログ村

　Power Queryには旧エクセルの「104万行」の壁がありません。ですから大量のデータも手軽に扱えるのが魅力です。ところが、Power Queryエディタ（以降、エディタ）では、旧エクセルのように、ショートカットキーを活用してデータ内を端から端まで散策するようなことができません。ですので、エディタ内ではデータ全体を把握しずらいのです。

しかも行数が1000行以上だと全ての行は表示されないようになっています

これは大量のデータがあっても、素早く動くようにするための工夫ですが、ユーザーにとっては不便です。

　但し「散策」できない点を補完する機能がエディタ内の「表示タブ」にあります。今回は「表示タブ」での「補完機能」について解説します

今回解説する補完機能の内容は次の３つになります

１．データの列数を1目で把握する

２．データの行数を1目で把握する

３．エラーの発生数を1目で把握する

　上の３つは、どれも「表示」タブ内の簡単な操作で把握できる内容になっています

では、今回解説する内容について紹介したところで、詳細な解説をはじめます

１．列数を把握する

「一体、このデータはどこまで右に続くのか？」

列数があまりに多いと、最終列を探す旅にでかけなくてはなりません

前述の表示タブでは、少なくとも「列数」はすぐに把握できます

表示タブの左下に列数が表示されています

こちは、行数についても「1,000行以下」であれば表示されています

２．データの行数を1目で把握する

Power Queryのエディタ内では、1000行以上は表示されないように工夫されています

ですから、フィルタで１０００行以上を見ようとしても上の画像のように「値が上限の1000個に到達しました」と表示されます

　Power Queryでは過去の記事で紹介したように、簡単にグループ化して数字を集計することができます。但し、グループ化した際には、元データの1部の列は非表示になります。

　実は、グループ化には「すべての行」というオプションがあり、こちらのオプションを選択すると「グループ化した数字」と「グループ化していない数字」を並列に表示できます

　今回は更に、並列に表示した数字間で差（「グループ毎の平均」－「個の平均」）を集計します

この差とはつまり数字のバラツキです

バラツキを自動抽出することで、数字の分析に役立てるようにします

では今回、解説に使用する元データとアウトプットする内容について解説します

次の画像の画像は今回使用するデータです

こちらのデータからクエリを作成し、エクセルシートに次のように出力します

①グループ平均

グループ化により、部門ごとの平均給料を集計します

②すべての行

①の部門ごとの数字と並行して、元のデータ（個）を表示します

③差

②の個の数字から①のグループ平均の数字の差を集計します

　

上記の①~③で今回のアウトプットの内容について解説しました

では、①～③の順に詳細な解説を行っていきます

①グループ平均

こちらは過去の記事で既に解説した内容になります

①では部門毎に、給料の平均額を計算します

つまり、グループ化する項目は「部門」、集計する項目は「給料」になります

上の画像のように、元のデータをテーブル化して、Power Querエディタ（以降、エディタ）を開いた後、ホームタブから「グループ化」をクリックします

「グループ化」をクリックした後に、上の画像の画面が開きますので、こちらで４つの項目を指定します

・グループ化する項目➡部門

・新しい列名➡給料・部門平均

・操作➡平均

・列（集計する列）⇒給料

上記の４つの項目を指定して、OKボタンを押すと次の画像の画面のようにグループ毎の平均値が集計されます

②すべての行

①で行ったステップに修正を加えて、元のデータの個々の数字を表示します

まずは、①で行ったステップの右横のマークをクリックします

上のGIF画像内で開いた「グループ化」画面で再度、グループ化条件を設定します

まずは、下の画像の画面上の「詳細設定」を指定します

上の画像のように「詳細設定」を指定すると、下の画像の下にある黄色の箇所のように「集計の追加」を行えるようになります

こちらの「集計の追加」をクリックすると新しい列名が指定できるようになります

次に、追加された新しい列の「操作」にて、下の画像のように「すべての行」を指定します

ちなみに、下の図の右にある、本来は集計する列を指定する箇所は「ブランク」のままでいいです

では、新しい列名を上の画像のように指定したところで、OKボタンをクリックします

すると、上のGIF画像のように新たな列が追加されます

次に、上の画像の右上にある「黄色の箇所」のマークをクリックします

すると、上の画像の画面が開きますので、こちらで黄色の箇所を設定します

・氏名➡チェック

・給料➡チェック

・元の列名をプレフィックスとして使用します➡チェックを外す

上の３つを指定したら、画面右下のOKボタンをクリックします

すると、上のグループGIF画像のように「氏名」と「給料」の列が右横に展開します

これで、①で「グループ化した項目」と「個々の元データ」が並列で表示されるようになりました

③差

上の②で 、①で「グループ化した項目」と「個々の元データ」が並列で表示されるようになりましたので、互いの差を計算します

計算式は下のようになります

ⅰ）個々の元データ ＜マイナス＞ ⅱ）グループ化した項目

実は上の式がとても重要です

差の計算は下の画像の画面で行うのですが、計算対象となる列指定の順番が重要です

計算式の左からⅰ）⇒ⅱ）の順番で列を指定します

列の指定はCtrlキーで行います

仮に、ⅱ）からⅰ）の順番で列指定をすると「ⅱ）⁻ⅰ）」の計算式で差が計算されます

差が集計できたところで、エクセルシートに読込むのですが、その前に２つ処理を行います

まず、２つの列の「列名」を下の図のように修正します

次に、グループの平均を集計した列の数字を丸めておきます

では、２つの処理を行ったので、エクセルシートに次の画面から「読込先」を指定して読み込み処理を行います

次のGIF画像が実際に「読込処理」を行った時の画像です

＜まとめ＞

　今回は、過去に解説したグループ化を更に踏み込んで、個々の元データも並列で表示する方法を解説しました

　個々の元データを表示するには、グループ化を設定する画面で「すべての行」を指定します

　更に、個々の元データを表示した後は、グループの平均と個々の元データの差を自動集計しました

個々の平均との差分を集計するだけでも、数字全体の特徴は掴みやすくなります

　仮に、Power Queryを使用しないで集計しようとすれば、ピボットテーブルやエクセル関数を組み合わせて処理を行わねばなりません

　このブログでは今後、Power Queryを分析作業に直接役に立つような手法も発信していきますので、よろしくお願いします！

長文を最後まで読んでくださり、誠にありがとうございました

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

にほんブログ村

　今回は、関数を使用せずにyyyy/mm/dd hh:mm形式の時刻データから「日付」と「曜日」データを作成する方法を紹介します

日付と曜日データは更に、両者を組み合わせて新たな列を作成します

　今回紹介する方法は、直感的なクリック操作のみで終了しますのでとても簡単です！

　解説は、次の画像のデータをPower Queryエディタ（以降、エディタ）に読込んだところからはじめます！

１．日付データの追加

勤務開始時刻列を選択した後に「列の追加」タブで「日」をクリックします

そうすると、次の画像のように「日」の列が新たにできます

ちなみに「日付のみ」をクリックすると、yyyy/mm/dd形式になります

２．曜日データの追加

まずは勤務開始時刻列を選択します

①と同じ画面ですが、今度は「日」より下の「曜日名」をクリックします

すると次の画像のように「曜日」が新しい列に出力されます

３．日付と曜日の組み合わせ

こちらは１．と２．の列を組み合わせる「列のマージ」処理を行います

こちらのメニューも「列の追加」タブにあります

「列のマージ」をクリックすると次の画像の画面が開きます

こちらの画面で３つ指定します

・区切り記号➡カスタム記号

・カスタム記号➡「 日 /」（2段目の欄です）

・新しい列名➡日付/曜日

３つ指定したら右下のOKボタンを押します

＜まとめ＞

今回は時刻データから日付データと曜日データを作成する方法を解説しました

本来は関数が必要な処理をクリック操作でできるので、とても便利です

　後、「列のマージ」もデータ整理（データクリーニング）に使えますのでこちらもぜひ活用してください

今回は以上です

最後までこの記事をお読みくださいありがとうございました

にほんブログ村