この記事では、無料で非常に便利ですが、まだあまり知られていないPower Queryと呼ばれるMS Excelアドインの機能について説明します。
Power Queryを使用すると、さまざまなソース（csv、xls、json、テキストファイル、これらのファイルを含むフォルダー、さまざまなデータベース、Facebook opengraph、Google Analytics、Yandex.Metrica、CallTouchなどのさまざまなAPIなど）からデータを収集できます、このデータを処理する繰り返し可能なシーケンスを作成し、それらをExcelテーブルまたはデータモデル自体にロードします。
そして、カットの下で、可能性のこのすべての素晴らしさの詳細を見つけることができます。
互換性と技術的な詳細
Power Queryは、Windows Excel 2010、2013のすべてのバージョンで無料で利用でき、Windows Excel 2016にはデフォルトで組み込まれています。MacOSXユーザーの場合、Power Queryは利用できません（ただし、これがなくても、Mac Excelは、 Parallelsを介した通常のExcelまたはリモートWindowsマシンでの実行）。
また、Power Queryは新しいビジネスインテリジェンス製品であるPower BIに統合されており、Power Queryが他のマイクロソフト製品に登場するという噂もあります。 つまり Power Queryには明るい未来があり、Microsoftテクノロジーの支持者（だけでなく）が開発に取りかかるときです。
仕組み
Power Queryをインストールすると、Excel 2010-2013インターフェイスに同じ名前の別のタブが表示されます。
新しいExcel 2016では、[取得]および[変換]ブロックの[データ]タブでPower Query機能を使用できます。
まず、Excelインターフェイスで、特定のデータソースを選択する必要があります。その前に、ダウンロードしたデータの最初の行のプレビュー（エリア1）でPower Queryのウィンドウを開きます。 ウィンドウの上部には、データ処理コマンドを含むリボンがあります（エリア2）。 また、画面の右側（エリア3）には、データに適用されるすべてのアクションのシーケンスを含むパネルがあります。
パワークエリ機能
Power Queryには多くの機能があり、お気に入りのいくつかについて詳しく説明します。
上で書いたように、Power Queryはさまざまなデータソースに接続できるという点で注目に値します。 したがって、CSV、TXT、XML、jsonファイルからデータをロードできます。 さらに、同じCSVファイルをダウンロードするためのオプションを選択するプロセスは、標準のExcelツールで実装されるよりも柔軟で便利です。多くの場合、エンコードは正しく正しく選択され、列区切り文字を指定できます。
フォルダー内のファイルを結合する
Power Queryは、指定されたフォルダーからデータを取得し、そのコンテンツを単一のテーブルに結合できます。 これは、たとえば、特別なレポートを別の期間に定期的に受信するが、分析用のデータが共通のテーブルに必要な場合に便利です。 GIF
テキスト関数
Power Queryのテキストの列の場合、リボンのボタンをクリックして、次のような機能を適用できます。
- 列を文字または文字数で分割します。 また、Excelとは異なり、列の最大数と、文字を読み取る方向（左、右）を指定できます。
- 列内のセルの大文字と小文字を変更する
- 列のセル内の文字数を数えます。
数値関数
リボンのボタンをクリックして数値を含む列の場合、次を適用できます。
- 算術演算
- 度に上げ、対数、階乗、根を計算します
- 三角演算
- 値を設定するために丸める
- パリティなどを決定する
日付、時刻、期間を操作するための関数
リボンのボタンをクリックして日付と時刻の値を含む列に、次を適用できます。
- 入力された日付の形式を自動的に検出します（Excelでは、これは大きな苦痛です）
- ワンクリックで、月の数、曜日、期間の日数または時間数などを抽出します。
ピボット解除-ピボット
Power Queryインターフェースには「アンピボット」機能があり、1クリックで、ピリオドごとに列に配置された1つのメトリックのデータを、ピボットテーブルで使用するのに便利な形式にすることができます（書きにくいことを理解しています-例参照）。 また、ピボットの逆のアクションを持つ関数があります。 GIF
Operation Merge-VLOOKUPの死
VLOOKUP関数は、MS Excelで最も使用される関数の1つです。 単一のキーを使用して、別のテーブルから1つのテーブルにデータをプルできます。 Power Queryのこの機能には、Merge操作という非常に便利な代替手段があります。 この操作では、キーボード入力なしで文字通り7回のマウスクリックで、複数のテーブルのテーブルをキー（単一または複合キー、複数の列で一致する必要がある場合）で1つに結合します。
Merge操作はSQLの結合に類似しており、結合が異なるタイプ（内部（デフォルト）、左外側、右外側、完全外側）になるように構成できます。
更新：ここで、Power QueryはAproximate参加を行うことはできませんが、できることを求められました。 確かに、箱から出してすぐに選択肢はありません。 GIF
さまざまなデータベースへの接続。 クエリの折りたたみ。
Power Queryは、MS SQLやMySQLからPostgresやHP Verticaまで、さまざまなデータベースにしがみつくことができる点でも注目に値します。 この場合、SQLや他のデータベース言語を知っている必要さえありません。 データプレビューはPower Queryインターフェイスに表示され、インターフェイスで実行されるすべての操作は透過的にデータベースクエリ言語に変換されます。
また、Power QueryにはQuery Foldingの概念があります：互換性のあるデータベース（現在はMS SQl）に接続している場合、Power Queryはサーバー側で大量のデータ処理操作を実行し、処理されたデータのみを取得しようとします。 この機能は、多くの治療のパフォーマンスを劇的に改善します。
プログラミング言語「M」
Power Queryアドオンは、スクリプト化されたデータ専用の新しいプログラミング言語Mのインタープリターです。
Power Query GUIでデータを使用して実行する各アクションに対して、新しいコード行がスクリプトに書き込まれます。 これを反映して、一連のアクションを含むパネル（エリア3）で、会話名を持つ新しいステップが作成されます。 これにより、一連のアクションを備えたパネルを使用して、各処理ステップでデータがどのように見えるかを常に確認できます。新しいステップを追加したり、特定のステップで適用された操作の設定を変更したり、順序を変更したり、不要なステップを削除したりできます。 GIF
また、記述されたスクリプト自体のコードをいつでも確認および編集できます。 そして、次のようになります。
残念ながら、M言語はExcelやMDXの式言語とは異なり、幸いなことにVisual Basicとは異なります。 ただし、習得するのは非常に簡単で、グラフィカルインターフェイスを使用して利用できないデータを操作する絶好の機会を提供します。
Yandex.Metrica、Google Analytics、その他のAPIからデータをダウンロードする
「M」言語を少しマスターしたので、Yandex.MetricaおよびGoogle Analytics APIに接続し、そこから指定された設定でデータを取得できるプログラムをPower Queryで作成できました。 プログラムPQYandexMetrikaおよびPQGoogleAnalyticsは、GPLライセンスの下でgithubにオープンソースを置きました。 使用することをお勧めします。 そして、これらのプログラムが愛好家によって完成されることを非常に嬉しく思います。
Googleアナリティクスの場合、さまざまな実装でこの種のエクスポーターが非常に多くありますが、Yandex.Metricaの場合、私のエクスポーターは最初に公開され、無料でさえありました:)
Power Queryは、投稿のヘッダーを生成し、リクエストを取得し、インターネットからデータを取得できます。 これにより、適切なレベルの器用さで、Power QueryはほぼすべてのAPIに接続できます。 特に、研究のために、CallTouch API、Rescuetimeコンピューターアクティビティ監視サービスAPIからクライアントの電話に関するデータを取得し、関連情報を取得するために必要なWebページの解析に取り組んでいます。
繰り返し性とアプリケーションオプションについてもう一度
上で書いたように、Power Queryスクリプトは、データに適用される操作の反復可能なシーケンスです。 これは、必要な処理を設定したら、スクリプトの1ステップだけを変更することで新しいファイルに適用できることを意味します-新しいファイルへのパスを示します。 これにより、膨大な量のルーチンを取り除き、生産的な作業のための時間を解放することができます-データ分析。
私はウェブ分析とコンテキスト広告をしています。 そして、そのインターフェイスでPower Queryに出会った瞬間から、Excel自体よりも多くの時間を費やしました。 私にとってはより便利です。 同時に、MS Excelのもう1つの優れたアドインであるPowerPivotの消費も増えました。
Power Queryを使用して行うタスクの一部を次に示します。
- Thickプロジェクトのセマンティクスを分析し、
- 周波数辞書を作成し、
- 特定のスライスを分析するためのWeb分析ダッシュボードとレポートを作成し、
- プロジェクトで構成されていない場合、Web分析システムの目標達成を復元します。
- Andrey Belousov（+ Bayes :)の方法で確率予測を平滑化します。
- K50統計からのデータのコンテキスト広告の監査を行い、
- そして、一度だけ実行する必要がある他の多くの異なるアドホック分析タスク
Yac / M 2015（ ビデオ ）で話したbiシステムは、Power Queryで完全に作成し、PowerPivot内にデータをロードしました。
ローカライズに関するいくつかの言葉
ロシアのユーザー向けのMicrosoft Webサイトでは、デフォルトで、ロシア語に翻訳されたインターフェースを備えたPower Queryがダウンロードされます。 幸いなことに、ローカライザーはプログラミング言語のロシア語への翻訳に至りませんでした（Excelの式言語で行われたように）が、あいまいな翻訳のユーザーの生活は非常に困難でした。 また、Power Queryの英語版をダウンロード、インストール、使用することをお勧めします。 私を信じて、それははるかに明確になります。
関連リンク
英語の公式リソース：
この記事をロシア語で書いている時点では、Power Queryに関する情報はほとんどなく、私が見つけたものを以下に示します。
- テーブルからGoogleスプレッドシートにデータをインポートする
- Power BI REST APIからデータを取得する
- Excel、Power Pivot、およびPower Query Facebook Advanced Community
- Power Queryに関するMarketing-wikiのページを更新
英語で：