ExcelのGet＆Transform機能の調査

エグゼクティブサマリー

データレイクとペタバイト規模のデータベースのこの時代において、CSV、テキスト、およびExcelファイルの形式でデータを受信する頻度が非常に高いことは衝撃的です。 現代の分析は機械学習アルゴリズムの最先端の進歩に焦点を当てていますが、データ分析の日々の煩わしさは、依然として、異種のデータ型を見つけ、コンパイルし、ラングリングする手動のプロセスです。

金融アナリストの場合、データはExcelスプレッドシートとして届くことがよくありますが、同じように、CSVへのデータダンプまたはSQLデータベースへのクエリです。 場合によっては、データがわかりにくいレイアウトで配置されていたり、分析に必要なすべてのコンポーネントが含まれていないことがあります。 このデータのスクラブに費やされた時間は、アナリストにとって貴重な時間ですが、このタスクは、許容される必要のある悪として受け入れられる場合があります。

Get＆Transformは何をしますか？

この一般的な問題の解決策は、実際には非常にアクセスしやすくなっています。ExcelとPower BIには、Get＆Transform（旧称Power Query）という名前の、ほとんどのユーザーが認識していないデータ変換ツールのセット全体があります。 埋め込まれた抽出、変換、および読み込み（ETL）機能を使用すると、金融アナリストはデータソースにシームレスにリンクし、より迅速に洞察を得ることができます。

データをティーアップしてExcelまたはPowerBIにロードするとき、通常、データに対していくつかの変換を実行する必要があります。 データ操作の例としては、次のものがあります。

• 列を削除し、
• データのフィルタリング、
• データをグループ化して、
• データのピボット/ピボット解除、
• 文字列を部分文字列に分割し、
• 文字列からキーワードを抽出し、
• 別のテーブルから行を追加し、
• 2つのディメンションテーブルを結合します。

次の図では、Get＆Transformが、データをロードする前にデータを前処理するという面倒な役割を果たしていることがわかります。

Get＆Transformを使用する必要があるのはなぜですか？

Get＆Transformの使い方を学ぶ価値があるのはなぜですか？ さて、私がこの機能を個人的に使用した目的を見ると、次のような柔軟なツールセットが提供されています。

• テキストファイルのフォルダ全体を単一のデータテーブルにロードする
• エクスポートされた会計ファイルを消化可能なレイアウトに変換する
• 何百万もの販売行をPowerPivotに直接ロードする
• Excelにインポートする前に、日次データを管理可能な月次結果にグループ化する
• 一致する列を結合して、別のテーブルのデータをスプライスする

通常、新しいデータを受け取ったら、Power Pivotにロードする前に、Get＆Transformを使用してデータを探索します。 これにより、どのような変換が必要かを確認し、データに対していくつかのピボットとグループ化をすばやく実行して、分析用のフレームワークを作成できます。 多くの場合、この段階で、より多くのデータが必要であるか、データの問題があることがわかります。 Excelベースのプラットフォームを使用することで、データソースをすばやく反復処理して、これらのデータの異常を見つけることができます。

最終的に、Excelにとどまるか、データ分析を別のプラットフォームに移動するかの決定は、対象者と分析の再現性と分布に依存します。 クライアントがExcelのみを使用している場合、ほとんどの場合、Get＆Transformを使用してデータを読み込み、Power Pivotを使用して分析を実行し、Excelを使用してピボットテーブルとグラフを作成します。 クライアントにとっては、すべてExcel内に格納されているため、これはシームレスに感じられます。

ただし、私のクライアントの場合：

1. 別の視覚化ツールを使用したい、
2. データを更新する複数のユーザーがいる、または
3. 機械学習モデルを採用する必要があり、

次に、Get＆Transformを最初のデータ探索にのみ使用し、次に重いものをRに移動します。

ExcelまたはPowerBIでGet＆Transformにアクセスする方法

以前のバージョンのExcelでは、PowerQueryはETL機能を支援するためにインストールできるアドインでした。 ただし、Excel2016とPowerBIでは、これらのツールはより緊密に統合されています。 Excel 2016では、[データ]タブ、[データの取得と変換]セクションからアクセスできます。

Power BIの機能は、[ホーム]タブの[外部データ]セクションにあります。

この記事では、私の例はPower BIで行われますが、インターフェイスはExcelのものとほとんど同じです。 違いが生じたときに指摘するので、チュートリアルは両方のタイプのユーザーにとって意味のあるものになるはずです。

1.CSVファイルの読み込み

このチュートリアルを支援するために、アウトドア用品や衣料品を販売する架空の小売業者の販売データの例をいくつか作成しました。 これらの各例では、データダンプの現実的な方法を示すために、さまざまな方法でデータが生成されます。

最初の例として、CSVファイルへの大きなデータダンプとして表示されるデータを確認します。 複雑な要因は、データがさまざまなストアを表す複数の列で表示されることです。 理想的には、データをインポートして、より使いやすいレイアウトに変換したいと考えています。

以下は、生のCSVがどのように見えるかのスクリーンショットです。

なぜこれを変更したいのですか？ これらのアプリケーションで可能な関係機能を利用するため。 これについては、ディスカッションでさらに詳しく説明します。

今のところ、データを「広くて短い」構造ではなく、「狭くて高い」構造として見る必要があると仮定しましょう。 最初のステップはCSVをロードすることです。 次に、データの「ピボット解除」を開始します。

ご覧のとおり、データの最終的な構造は最初のデータよりも狭く、はるかに長くなっています。 もう1つのポイントは、さまざまなアクションをクリックしているときに、右側のツールがクエリの作成に使用された適用済みステップのリストを生成していることです。 後で再検討するため、これはバックグラウンドで行われていることを理解することが重要です。

Get＆Transformは、ほとんどの場合、PowerBIとExcelの間で外観と動作が同じです。 ただし、Excelでは、[閉じて読み込む]をクリックした後、追加のプロンプトが1つあります。 次の図では、データをロードするかどうかを切り替えることができます。

1. Excelのテーブル、
2. データに対して作成されたピボットテーブル、
3. データに対して作成されたピボットグラフ、または
4. 「接続のみを作成してください。」

さらに、このデータをデータモデルに追加するかどうかのオプションもあります。 このチェックボックスをオンにすると、データがPowerPivotテーブルに読み込まれます。 Power Pivotでデータを分析する場合は、[接続の作成のみ]を選択し、[このデータをデータモデルに追加する]オプションが選択されていることを確認することをお勧めします。 データがExcelの行制限内にあり、Excelで分析を実行する場合は、[テーブル]を選択します。

次のクリップでは、データを長くて細いものにフォーマットした理由は、店舗だけでなく地域や州ごとの売上を分析できるようにするためであることがわかります。 このタスクを実行するために、各ストアを地域と州にマップするテーブルをインポートします。 これらのさまざまなグループごとの売上を示すレポートをすばやく作成できることを以下に示します。

ExcelまたはPowerBIでのデータ変換のこのタイプの機能が、次のような動的なデータのグループ化がある場合にどのように強力に適用できるかを想像できます。

• 毎日のデータを週、月、四半期にロールアップします。
• 営業担当者を部門と地域にグループ化します。 また
• SKUを製品タイプにマッピングします。

この記事ではCSVおよびその他のExcelファイルについて説明しますが、Get＆Transformではさまざまなデータ型に取り組んでいます。 クエリが作成されると、データの変更に応じて時間の経過とともに更新できます。

2.テキスト文字列の処理

Get＆Transformの文字列操作機能を示すために、会社の総勘定元帳（GL）からの会計トランザクションを示すテキストファイルを模倣する別のデータセットを作成しました。

アカウント番号と名前が同じ文字列にどのように表示されるかに注意してください。 Power BIでは、アカウント番号と名前を別々のフィールドに簡単に解析できます。

このビデオでは、列を分割した後、ツールが[アカウント]フィールドの新しい左側が数値であると推測し、「変更されたタイプ1」ステップを作成していることがわかります。 最終的にこのフィールドを文字列として必要とするため、先に進んで、適用されたステップの下でステップを手動で削除できます。

次に、同じデータを取得して、アカウントカテゴリへのマッピングを含むアカウントのチャートを作成します。

なぜこれらすべての手順を実行して、いくつかのアカウント番号をマッピングするのでしょうか。 実際の総勘定元帳は、数百または数千のアカウントになる可能性があります。 このクイックマッピングクエリは、これまでに示したように、追加の作業なしでそのレベルにスケーリングされます。

3.さまざまなデータソースの操作

Get＆Transformは、さまざまなデータソースをサポートしています。 完全なリストではありませんが、以下にいくつかの例を示します。

個人的には、上記のリストにある接続の約半分しか試していません。 私が使用した各コネクタはかなり堅牢です。 面倒な作業をせずに、生データから洞察を得ることができました。 同様に重要なことは、異なるデータソース間のバリデーターとして機能し、最終出力が正規化されたレベルの品質管理を確実に行えるようにすることです。

4.M言語でコードをパーソナライズする

バックグラウンドでは、ツールのボタンをクリックするか選択するたびに、Get＆Transformがコードを生成します。 以下は、作成したアカウントマッピングクエリのコードにアクセスする方法の例です。

このコードは、基本的なユースケース用に自動生成されるMという名前の関数型言語を使用しています。 ただし、より複雑なデータラングリングの場合は、独自のコードを編集および記述できます。 ほとんどの場合、私はこのコードに小さな変更を加えるだけです。 より複雑な変換では、ほとんどのコードを最初からステージ一時テーブルに記述したり、より複雑な結合を実行したりする場合があります。

Get＆Transformの限界

100万行を超える行をエクスポートしようとすると、Excelは限界に達する傾向があります。 Get＆Transformを使用して数百万の行を変換した場合、グループ化されていない行を送信する唯一の方法は、面倒なハックまたは回避策を使用することです。 また、Get＆Transformクエリは、特に複数のデータソースと結合を使用している場合、複数のユーザーにデプロイするのが不安定になる可能性があることもわかりました。 そのような場合、私は常にRを使用して複製可能なデータラングリングを展開します。 最後に、Excelはより高度なデータモデリング用に構築されていません。 線形回帰は非常に迅速に実行できますが、それを超えると、より厳密なプラットフォームを使用する必要があります。

そうは言っても、ほとんどのクライアントが最も快適なのはExcelだと思います。 Excelは、依然として金融アナリストの武器庫で最も重要なツールです。 Get＆Transform機能を組み込むことにより、ExcelとPower BIは、受け入れることができるさまざまなデータソースを通じてさらに強力になります。