データレイクとペタバイト規模のデータベースのこの時代において、CSV、テキスト、およびExcelファイルの形式でデータを受信する頻度が非常に高いことは衝撃的です。現代の分析は機械学習アルゴリズムの最先端の進歩に焦点を当てていますが、データ分析の日々の煩わしさは、依然として、異種のデータ型を見つけ、コンパイルし、ラングリングする手動のプロセスです。
金融アナリストの場合、データはExcelスプレッドシートとして届くことがよくありますが、同じように、CSVへのデータダンプまたはSQLデータベースへのクエリです。場合によっては、データがわかりにくいレイアウトで配置されていたり、分析に必要なすべてのコンポーネントが含まれていないことがあります。このデータのスクラブに費やした時間は、アナリストにとって貴重な時間ですが、このタスクは、許容される必要のある悪として受け入れられることがあります。
この一般的な問題の解決策は、実際には非常にアクセスしやすくなっています。ExcelとPower BIには、Get＆Transform（旧称Power Query）という名前の、ほとんどのユーザーが認識していないデータ変換ツールのセット全体があります。埋め込まれた抽出、変換、および読み込み（ETL）機能を使用すると、金融アナリストはデータソースにシームレスにリンクし、より迅速に洞察を得ることができます。
データをティーアップしてExcelまたはPowerBIに読み込むときは、通常、データに対していくつかの変換を実行する必要があります。データ操作の例としては、次のものがあります。
次の図では、Get＆Transformが、データをロードする前にデータを前処理するという面倒な役割を果たしていることがわかります。
Get＆Transformの使い方を学ぶ価値があるのはなぜですか？さて、私がこの機能を個人的に使用した目的を見ると、次のような柔軟なツールセットが提供されています。
通常、新しいデータを受け取ったら、Power Pivotにロードする前に、Get＆Transformを使用してデータを探索します。これにより、どのような変換が必要かを確認し、データに対していくつかのピボットとグループ化をすばやく実行して、分析用のフレームワークを作成できます。多くの場合、この段階で、より多くのデータが必要であるか、データの問題があることがわかります。 Excelベースのプラットフォームを使用することで、データソースをすばやく反復処理して、これらのデータの異常を見つけることができます。
最終的に、Excelにとどまるか、データ分析を別のプラットフォームに移動するかの決定は、対象者と分析の再現性と分布に依存します。クライアントがExcelのみを使用している場合、ほとんどの場合、Get＆Transformを使用してデータを読み込み、Power Pivotを使用して分析を実行し、Excelを使用してピボットテーブルとグラフを作成します。クライアントにとって、これはすべてExcel内に格納されているため、シームレスに感じられます。
ただし、クライアントの場合：
次に、Get＆Transformを最初のデータ探索にのみ使用し、次に重い作業をRに移動します。
以前のバージョンのExcelでは、PowerQueryはETL機能を支援するためにインストールできるアドインでした。ただし、Excel2016とPowerBIでは、これらのツールはより緊密に統合されています。 Excel 2016では、データからアクセスできます。 タブをクリックしてから、データの取得と変換 セクション。
Power BIでは、機能はホームにあります タブ、外部データ セクション。
この記事では、私の例はPower BIで行われていますが、インターフェイスはExcelとほぼ同じです。違いが生じたときに指摘するので、チュートリアルは両方のタイプのユーザーにとって意味のあるものになるはずです。
このチュートリアルを支援するために、アウトドア用品や衣料品を販売する架空の小売業者の販売データの例をいくつか作成しました。これらの各例では、データダンプの現実的な方法を示すために、さまざまな方法でデータが生成されます。
最初の例として、CSVファイルへの大きなデータダンプとして表示されるデータを確認します。複雑な要因は、データがさまざまなストアを表す複数の列で表示されることです。理想的には、データをインポートして、より使いやすいレイアウトに変換したいと考えています。
以下は、生のCSVがどのように見えるかのスクリーンショットです：
なぜこれを変更したいのですか？これらのアプリケーションで可能な関係機能を利用するため。これについては、ディスカッションでさらに詳しく説明します。
今のところ、データを「広くて短い」構造ではなく、「狭くて高い」構造として見る必要があると仮定しましょう。最初のステップはCSVをロードすることです。次に、データの「ピボット解除」を開始します。
ご覧のとおり、データの最終的な構造は最初のデータよりも狭く、はるかに長くなっています。もう1つのポイントは、さまざまなアクションをクリックしているときに、右側のツールがクエリの作成に使用された適用済みステップのリストを生成していることです。後で再検討されるため、これはバックグラウンドで行われていることを理解することが重要です。
Get＆Transformは、ほとんどの場合、PowerBIとExcelの間で外観と動作が同じです。ただし、Excelでは、[閉じて読み込む]をクリックした後 、追加のプロンプトが1つあります。次の図では、データをロードするかどうかを切り替えることができます。
さらに、このデータをデータモデルに追加するかどうかを選択することもできます。 。このチェックボックスをオンにすると、データがPowerPivotテーブルに読み込まれます。 Power Pivotでデータを分析する場合は、接続の作成のみを選択することをお勧めします。 次に、このデータをデータモデルに追加することを確認します。 オプションが選択されています。データがExcelの行制限内にあり、Excelで分析を実行する場合は、テーブルを選択します。 。
次のクリップでは、データを長くて細いようにフォーマットした理由は、店舗だけでなく地域や州ごとの売上を分析できるようにするためであることがわかります。このタスクを実行するために、各ストアを地域と州にマップするテーブルをインポートします。これらのさまざまなグループごとの売上を示すレポートをすばやく作成できることを以下に示します。
ExcelまたはPowerBIでのデータ変換のこのタイプの機能が、次のような動的なデータのグループ化がある場合にどのように強力に適用できるかを想像できます。
この記事ではCSVおよびその他のExcelファイルについて説明しますが、Get＆Transformではさまざまなデータ型に取り組んでいます。クエリが作成されると、データの変更に応じて時間の経過とともに更新できます。
Get＆Transformの文字列操作機能を示すために、会社の総勘定元帳（GL）からの会計トランザクションを示すテキストファイルを模倣する別のデータセットを作成しました。
アカウント番号と名前が同じ文字列にどのように表示されるかに注意してください。 Power BIでは、アカウント番号と名前を別々のフィールドに簡単に解析できます。
このビデオでは、列を分割した後、ツールが[アカウント]フィールドの新しい左側が数値であると推測し、「変更されたタイプ1」ステップを作成していることがわかります。最終的にこのフィールドは文字列として必要になるため、先に進んで、適用されたステップの下でステップを手動で削除できます。
次に、同じデータを取得して、アカウントカテゴリへのマッピングを含むアカウントのグラフを作成します。
なぜこれらすべての手順を実行して、いくつかのアカウント番号をマッピングするのでしょうか。実際の総勘定元帳は、数百または数千のアカウントになる可能性があります。このクイックマッピングクエリは、これまでに示したように、追加の作業なしでそのレベルにスケーリングされます。
Get＆Transformは、さまざまなデータソースをサポートしています。完全なリストではありませんが、以下にいくつかの例を示します。
テキストファイルExcelFacebookAdobe Analytics Google Analytics Salesforce Azure Redshift Spark SQL Server SAP HANA Teradata Google BigQuery個人的には、上記のリストにある接続の約半分しか試していません。私が使用した各コネクタはかなり堅牢です。面倒な作業をせずに、生データから洞察を得ることができました。同様に重要なのは、異種のデータソース間のバリデーターとして機能し、最終出力が正規化されたレベルの品質管理を確実に行えるようにすることです。
バックグラウンドでは、ツールのボタンをクリックするか選択するたびに、Get＆Transformがコードを生成します。以下は、作成したアカウントマッピングクエリのコードにアクセスする方法の例です。
このコードは、基本的なユースケース用に自動生成されるMという名前の関数型言語を使用しています。ただし、より複雑なデータラングリングの場合は、独自のコードを編集および記述できます。ほとんどの場合、私はこのコードに小さな変更を加えるだけです。より複雑な変換では、ほとんどのコードを最初からステージ一時テーブルに記述したり、より複雑な結合を実行したりする場合があります。
100万行を超える行をエクスポートしようとすると、Excelは限界に達する傾向があります。 Get＆Transformを使用して数百万の行を変換した場合、グループ化されていない行を送信する唯一の方法は、面倒なハックまたは回避策を使用することです。また、Get＆Transformクエリは、特に複数のデータソースと結合を使用している場合、複数のユーザーにデプロイするのが不安定になる可能性があることもわかりました。そのような場合、私は常にRを使用して複製可能なデータラングリングを展開します。最後に、Excelはより高度なデータモデリング用に構築されていません。線形回帰は非常に迅速に実行できますが、それを超えると、より厳密なプラットフォームを使用する必要があります。
そうは言っても、ほとんどのクライアントが最も快適なのはExcelだと思います。 Excelは、依然として金融アナリストの武器庫で最も重要なツールです。 Get＆Transform機能を組み込むことにより、ExcelとPower BIは、受け入れることができるさまざまなデータソースを通じてさらに強力になります。