財務プロセス

ExcelのGet＆Transform機能の調査

エグゼクティブサマリー

Get＆Transformとは何ですか？

Get＆Transformは、MicrosoftExcelおよびPowerBIソフトウェアパッケージ内で使用するためのデータ変換ツールです。
多くの場合、データは非構造化形式で到着するため、ETL（抽出、変換、および読み込み）プロセスは手作業による回避策の面倒なプロセスになります。
Get＆Transformは、このような生データのクリーニングと整理のプロセスを自動化および迅速化し、最終的には観察と傾向を明らかにする分析タスクを支援します。
Get＆Transformが提供する機能の例としては、列の削除、データのグループ化、文字列の部分文字列への分割、別のテーブルからの行の追加などがあります。
Excelユニバース内でワークフローを維持するための、Get＆Transformは、関連する利害関係者に簡単に説明およびデモンストレーションできる優れたツールです。

Get＆Transformを使用するにはどうすればよいですか？

Excelでのアクセスは データの取得と変換 内のセクション データ タブ。 Power BIでは、 外部データ のセクション ホーム タブ。
CSVの読み込み：Get＆Transformを介してCSVをインポートすると、データのピボットを支援するために、CSVをクリーンアップして「狭く」または「広く」することができます。これらの指示は保存して、将来のインポートのために繰り返すことができます。
テキスト文字列の処理： 列へのテキスト Excelの機能であるGet＆Transformは、結合されたテキストと数値の文字列をすばやく解析して、別々の列に分割できます。
さまざまなデータソース：さまざまな入力ファイルが受け入れられるため、一貫性のある正規化された出力品質を維持しながら、さまざまなソースを操作できます。
コードによるカスタマイズ：M言語は、Get＆Transform内で使用される関数型コードであり、より多くのオーダーメイドのリクエストに対してカスタムクエリを作成することができます。

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

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

Get＆Transformは何をしますか？

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

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

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

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

データの前処理を実行するExcelの取得と変換の図

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

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

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

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

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

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

cvvでハッキングされたクレジットカード

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

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

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

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

Excel2016から取得および変換機能にアクセスする方法のスクリーンショット

Power BIでは、機能は ホーム タブ、 外部データ セクション。

PowerBIの[外部データ]セクションの[ホーム]タブから取得および変換機能にアクセスする方法のスクリーンショット

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

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

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

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

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

CSVから視覚化された生データのスクリーンショット

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

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

ロードおよびピボット解除プロセスのデモンストレーション

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

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

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

さらに、私たちはまたするかどうかのオプションが与えられます このデータをデータモデルに追加します 。このチェックボックスをオンにすると、データがパワーピボットテーブル。 Power Pivotでデータを分析する場合は、選択することをお勧めします 接続のみを作成する そして、 このデータをデータモデルに追加します オプションが選択されています。データがExcelの行制限内にあり、Excelで分析を実行する場合は、[ テーブル 。

給与を契約レートに変換する

[データのインポートオプション]ウィンドウのスクリーンショット

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

地域と州のグループ化を使用した、迅速で簡単なレポート作成のデモンストレーション

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

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

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

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

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

会社の総勘定元帳からの会計取引を示す表

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

アカウント番号と名前を別々のフィールドに解析するデモンストレーション

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

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

データからアカウントカテゴリへのマッピングを使用してアカウントのチャートを作成するデモンストレーション

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

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

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

テキストファイル Excel フェイスブック Adobe Analytics グーグルアナリティクス Salesforce Azure Redshift スパーク SQLサーバー SAP HANA Teradata Google BigQuery

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

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

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

アカウントマッピングクエリのコードにアクセスする方法の例のデモンストレーション

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

Get＆Transformの限界

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

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