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

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

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

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

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

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

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

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

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

メリッサ・デ・コルテ

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

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

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

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

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

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

目次

Excel のピボット テーブル

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

データのピボット

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

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

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

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

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

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

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

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

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

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

結論

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

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

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

メリッサ


Power Automate の文字列関数: Substring と IndexOf

Power Automate の文字列関数: Substring と IndexOf

Microsoft フローで使用できる 2 つの複雑な Power Automate String 関数、substring 関数とindexOf 関数を簡単に学習します。

LuckyTemplates でビジュアル ツールチップを作成する

LuckyTemplates でビジュアル ツールチップを作成する

LuckyTemplates ツールチップを使用すると、より多くの情報を 1 つのレポート ページに圧縮できます。効果的な視覚化の手法を学ぶことができます。

Power Automate で HTTP 要求を行う

Power Automate で HTTP 要求を行う

Power Automate で HTTP 要求を作成し、データを受信する方法を学んでいます。

LuckyTemplates で日付テーブルを作成する方法

LuckyTemplates で日付テーブルを作成する方法

LuckyTemplates で簡単に日付テーブルを作成する方法について学びましょう。データの分析と視覚化のための効果的なツールとして活用できます。

2 つの方法による SharePoint 列の検証

2 つの方法による SharePoint 列の検証

SharePoint 列の検証の数式を使用して、ユーザーからの入力を制限および検証する方法を学びます。

SharePoint リストを Excel または CSV ファイルにエクスポート

SharePoint リストを Excel または CSV ファイルにエクスポート

SharePoint リストを Excel ファイルおよび CSV ファイルにエクスポートする方法を学び、さまざまな状況に最適なエクスポート方法を決定できるようにします。

Power Automate のオンプレミス データ ゲートウェイ

Power Automate のオンプレミス データ ゲートウェイ

ユーザーがコンピューターから離れているときに、オンプレミス データ ゲートウェイを使用して Power Automate がデスクトップ アプリケーションにアクセスできるようにする方法を説明します。

DAX 数式での LASTNONBLANK の使用

DAX 数式での LASTNONBLANK の使用

DAX 数式で LASTNONBLANK 関数を使用して、データ分析の深い洞察を得る方法を学びます。

CROSSJOIN 関数の使用方法 – LuckyTemplates および DAX チュートリアル

CROSSJOIN 関数の使用方法 – LuckyTemplates および DAX チュートリアル

LuckyTemplates で予算分析とレポートを実行しながら、CROSSJOIN 関数を使用して 2 つのデータ テーブルをバインドする方法を学びます。

TREATAS 関数を使用して LuckyTemplates で仮想リレーションシップを作成する

TREATAS 関数を使用して LuckyTemplates で仮想リレーションシップを作成する

このチュートリアルでは、LuckyTemplates TREATAS を使用して数式内に仮想リレーションシップを作成する方法を説明します。