テキスト ファイル内のディメンション テーブル: Power Query ソリューション

で現在進行中の「今週の問題」の問題 #2 について説明します。このフォーラムでは、乱雑なデータをクリーンなディメンション テーブルに並べ替える方法について議論しました。これは、LuckyTemplates で主催しているこの新しい取り組みの 2 回目です。私がこのシリーズに特に情熱を注いでいるのは、誰でも定期的に練習する機会が得られるからです。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

毎月第 1 水曜日にはDAX チャレンジがあり、第 3 水曜日にはパワー クエリ チャレンジがあります。

これは、LuckyTemplates 内で活用する必要があるこれらの言語について探索、発見、新しいことを学ぶ素晴らしい機会です。

LuckyTemplates フォーラムに「今週の問題」というカテゴリがあります。

主に焦点を当てるのはプロセスであり、結果にはあまり焦点を当てません。

問題をより小さな部分に分解することに成功すれば、直面している問題を解決できるようになります。

メリッサ・デ・コルテ

ブライアンは以前、ラバーダックデバッグと呼ばれる手法について話しました。見逃した方は、ぜひ彼のビデオをチェックしてください。これらの課題に行き詰まったときに役立ちます。

まず、目の前のタスクを調べてみましょう。乱雑なテキスト ファイルがあるので、これを適切なディメンション テーブルに変換する必要があります。

さて、このようなデータを見ると、ほとんどの場合、テキストのクリーニング、不要な文字の削除、トリミング、単語の大文字化などがすべてになります。

ただし、このスタック データからすべての行を取得し、国ごとに 1 行に変換する必要もあります。すべてのフィールド名が国ごとに 1 つの列で繰り返されるため、これをスタック データと呼んでいます。

私には Excel の使用経験があり、その最も強力な機能の 1 つはピボット テーブルであると考えています。

目次

• Excel のピボット テーブル
• Power Query を使用してディメンション テーブルを作成する
• カスタムテキストクリーニング機能
• データのピボット
• 結論

Excel のピボット テーブル

ピボット テーブルを使用すると、データをセグメントごとに表示できます。行セクションに何を配置するかに応じて、ピボット テーブルでは、そのセグメントの各出現箇所が 1 行に凝縮されます。

その後、フィールドを列セクションにドラッグすることで、それをさらに分割できます。

これがどのようなものであるかの例を作成しました。ここには、テキスト ファイルにあったデータとほぼ同じデータがあります。

次のページでは、空のピボット テーブルを作成しました。

さて、ここでお見せしなかったのは、すでにお見せした 2 つの列の代わりに、ここには 3 つの列があるということです。

セグメントを行にドラッグします。

列 1 にはフィールド名があったので、それを列セクションにドラッグします。

列 2 にはすべてのフィールド値が含まれていたため、それを値セクションにドラッグします。

ここで、ピボット テーブルがテキスト文字列を処理できないことがわかります。それらはカウントされますが、各フィールドに単一の値があることがわかります。

次に、私が作成したセグメントを見てみましょう。そこで、データに戻り、列を再表示します。これは、まだ積み重ねられたデータの個別のブロックをそれぞれ識別する単なるインデックス番号であることがわかります。

したがって、私にとって、キー変換はデータを表形式に戻すためのピボットになります。

フォーラムでは、これに対処する他の方法を見てきました。ピボット アクションを使用する以外にも表形式を取得する方法はありますが、それらは同様にうまく機能します。興味がある場合は、フォーラムにアクセスして探索を始めてください。

Power Query を使用してディメンション テーブルを作成する

パワークエリを調べて、解決策を確認してみましょう。

個人的には、UI が M コードの大部分をうまく書いてくれると思います。そのため、私はできる限りユーザー インターフェイスを使用してクエリを設計する傾向があります。

クエリが必要なことを実行したら、詳細エディターに移動して M コードを調べ、変更できるかどうかを確認します。それがどのようなものかを見てみましょう。

これは、ユーザー インターフェイスを使用して構築された私の基本グループです。

右側の適用されたステップには多くのステップがあることがわかります。

それ自体は問題ではありませんが、これらの手順を見るだけで、グループ化できる変換が多数あることがわかります。

高度なエディターを開いてみましょう。

このクエリには 31 のステップがあることがわかります。

31 のステップを含むこの同じクエリにいくつかのコメントも追加しましたが、セクションに分割しました。

最初に行ったのは、タイプの変更ステップを削除することでした。これらのテキスト変換手順をすべて実行するカスタム関数を作成することをお勧めします。

たくさんのコメントがありますが、このブログ投稿で強調したいことは 2 つだけです。1 つ目は、テキストのクリーニングを目的としたカスタム関数です。

2 番目は、このタイプのデータを適切な表形式に戻すピボット ステップです。

カスタムテキストクリーニング機能

クエリを作成する初期段階に戻ってみましょう。この段階では、列 1 と結合された列のテキストをクリーンアップするためのグループ化されたすべての手順がありました。

さらにカスタム列も追加しました。その唯一の目的は、カスタムのテキスト クリーニング機能を構築することです。マージされた列でそれを呼び出しました。

この方法では、関数を一度に記述する必要はなく、前のステップの結果を確認した後に新しい変換を追加しながら、一度に 1 ステップずつ徐々に作成していきます。

テキスト クリーニング関数の M コードを見てみましょう。

ご覧のとおり、複数のステップがあります。このテキスト関数を構築していたとき、クエリの間を行ったり来たりして結果を確認し、次に何を構築し、何を修正するかを確認しました。

この結果を基に、必要なすべての変換を実行しました。ここで使用した M 関数の一部は、テキストなど、最初のクエリを作成したときにユーザー インターフェイスによって提供されました。トリム機能。ただし、使用されている他の関数はそうではありません。

M 関数に詳しくない場合は、オンラインの M 公式ガイドですべてのM 関数を調べることができます。これは、移動する必要があるリンクです。

パワークエリと関数に特化したセクションがあります。

下にスクロールするとテキスト機能に関するセクションがあり、各セクションは概要から始まります。パワー クエリとフォーミュラ M 言語内のすべてのテキスト関数のリストがあります。

特定の変換を探している場合は、ここで検索できます。

データのピボット

このディメンション テーブルの作成時に強調したい 2 番目の部分は、データ自体のピボットです。これについても詳しく見てみましょう。

まずはインデックスを追加することから始めました。データのブロックを適切にセグメント化するためにインデックスを更新しました。これを行うには、列 1 に国というテキストが含まれる各行のインデックス番号を返し、その値を埋めます。

必要なのは、データ自体をピボットすることだけです。変換タブにはピボット列があります。列 1 を選択した状態で、「ピボット列」をクリックします。

列 1 の値を新しい列名として使用します。また、それらのフィールド名の値がどこにあるのかも知りたいと考えています。これらは統合された列にあります。

Excel がテキスト値を処理できる場合、Power Query も高度なオプション設定により処理できます。テキスト値を処理できるようにするには、[集計しない]を選択するだけです。

[OK] をクリックすると、データがピボットされたことがわかります。

結論

上の画像は最終結果のクエリです。提供されたテキスト ファイル内の乱雑なデータを再整形し、分析に適したきれいなディメンション テーブルに変換した方法を気に入っていただけたでしょうか。

このブログ投稿を気に入っていただけた場合は、新しいコンテンツを見逃さないように、LuckyTemplates チャンネルに登録してください。

今後の「今週の問題」チャレンジで皆さんにお会いできることを楽しみにしています。

メリッサ