区切り文字で区切られたデータ – LuckyTemplates を使用したレコードの抽出

複数の列内の結合された区切りデータからレコードを抽出する方法を説明します。これにより、データが分析により適した形式に変換されます。

これを行うにはいくつかの方法があります。ただし、このチュートリアルでは、LuckyTemplates コミュニティのメンバーである Ankit によって提案されたソリューションに焦点を当てます。このチュートリアルの完全なビデオは、このブログの下部でご覧いただけます。

目次

• Text.Split の使用
• List.Zip の使用
• データのクリーンアップ
• 結論

Text.Split の使用

このソリューションでは、 Power Query を使用して区切り文字で区切られたデータからレコードを抽出します。

以下は、Power Query内のOpportunitiesテーブルです。

ご覧のとおり、複数の値が 1 つのレコードに連結されています。たとえば、2 行目には 4 つの異なる値が表示されます。これら 4 つの値は、 [競合他社] 列と[競合他社の金額]列の下にまとめられます。

最初にText.Split を使用します。

Text.Split は、特定の区切り文字に基づいてテキスト値を分割した後のリストを返します。

Text.Splitの使用を開始するには、このテーブルで使用されている区切り文字をコピーします。

テーブル内のそのエントリをクリックすると、レコードの値が一番下に表示されることがわかります。そこから区切り文字をコピーします。

それが完了したら、カスタム列を追加します。

まだ名前を変える必要はありません。Custom Column FormulaでText.Splitを実行するだけです。

この数式にはテキスト値が必要です。そこで、右側のペインで [競合他社] 列を選択するだけで、自動的に数式に追加されます。

テキストとして指定される区切り文字も必要です。そこで、一対の引用符の中に、先ほどコピーした区切り文字を貼り付けます。次に、右括弧を追加します。

OK を押すと、リスト オブジェクトが作成されます。

最初のリストを見ると、「競合他社」列と「金額」列の両方にエントリが 1 つだけ表示されています。そのため、[カスタム] 列の下のリスト オブジェクトの横の空白スペースをクリックすると、下部に 1 つの値が表示されます。

2 番目のレコードには、4 つの値を含むリストがあります。これは、 [競合他社] 列と[金額]列にも表示される 4 つの値に対応します。

List.Zip の使用

リストを分割したので、次のステップは、[競合他社の金額] 列から対応する値を追加することです。そのためにList.Zipにアクセスします。

List.Zip を実際のジッパーと考えてください。リストのリストを取得し、項目を結合します。

以下の例を見て、最初のリスト (1 と 2) をジッパー上の緑色のトラックと考えてください。

次に、3 と 4 を赤いトラックと考えます。

List.Zipを適用すると真ん中でくっつきます。したがって、最初のグループの 1 は、2 番目のグループの 3 と結合されます。2 と 4 についても同じことが起こります。これは出力で確認できます。2 つの新しいグループが形成されています。

関連するリストの長さが異なる場合があります。これは、リストを結合すると、欠落しているデータ が返されることを意味します。データがないにもかかわらず正確なペアを作成するには、null 値が追加されます。

Opportunitiesテーブルに戻り、別のカスタム列を追加します。

今回は、 [Competitor Amounts]列を数式に追加します。

List.Zipも追加します。

以下を確認すると、エラーは検出されていないことがわかります。

しかし、[OK] をクリックして、形成された新しい列を見ると、やはりエラーが発生します。

エラーの横の空白をクリックして詳細を確認します。これは、リストに関するいくつかの引数を返したためにエラーが発生していることを示しています。

式を見ると、Competitors 列が参照されているこの構文がリストを返していることがわかります。

ただし、[競合他社の金額] 列の構文ではリストも返されます。

List.Zip の動作を思い出してください。List.Zip は、リストの単一のリストを取得し、それらを結合します。

2 つのリストが表示されていることがわかり、これらのリストをまとめるためにリスト初期化子が必要です。このため、中括弧を追加しています。

最後に閉じ中括弧を追加したら、Enter キーを押すだけです。これによりリストオブジェクトが得られます。

ここで、最初のレコードの横をクリックすると、単一のリスト オブジェクトが表示されます。これは正しいです。[競合他社の金額] の下に実際には 1 つの値があることが分かります。

2 番目のレコードに対して同じことを行うと、4 つのリストを含むリスト オブジェクトが表示されます。これは、[競合他社の金額] の下にある区切られたデータ内の値の数とも一致します。

[カスタム] 列の見出しの右側にある横矢印を使用して、データを新しい行に展開します。

新しい行に展開するときに、2 番目のレコードに注目してください。

そのレコードのすべての値が下に展開されていることがわかります。

空白部分の横をクリックすると、ネストされたリストの内容が表示されます。1 番目のレコードには各列に 1 つの値しかないため、同じデータを下部にも表示します。

しかし、2 番目のレコードをクリックすると、区切りデータで指定された 4 つの値のうちの 1 番目のみが表示されていることがわかります。

3 番目のレコードでは、2 番目の値の組み合わせを示します。

そこで、再び横向きの矢印を使用します。ただし、今回はそのリストから値を抽出します。

カスタムセパレーターを付けます。

区切り文字として二重パイプを使用します。これにより、区切られたデータ内の値の区別がより明確になります。

[OK] を押すと、値が [カスタム] 列に直接連結されます。

データのクリーンアップ

Competitor 列と Competitor Amounts 列はもう必要ないので、これらの列を削除します。

Custom 列については、分割するつもりです。

[変換] タブで、[列の分割] を選択します。区切り文字で区切っていきます。

窓には二重管をベースにしていきます。

OK を押すと、データが別々の列に分割されます。

データの内容を理解しやすくするために、これらの新しい列の名前を変更します。これを再び「競合他社」列と呼びます。

次に、もう一方の列を「金額」列と呼びます。

明らかに、「金額」列には数値が含まれています。しかし、データ型を表すアイコンを見ると、数字ではなくテキストがあることがわかります。それで、それを修正し始めます。

私はヨーロッパに拠点を置いているため、千の位の区切り文字としてカンマの代わりにピリオドを使用します。これをクリーンアップするために値の置換を使用します。

ウィンドウで、「検索する値」の下にカンマを入力し、「置換後の文字列」の下にピリオドを入力するだけです。

「OK」を押すと、すべてのカンマにピリオドが表示されるようになります。

次に、値の前にあるドル記号を削除します。もう一度、値の置換ツールを使用します。今回は「置換後の文字列」の部分を空白にします。

[OK] を押すと、値には通貨なしの数値のみが表示されます。

私のコラムを見てみると、ここにも二重ダッシュのエントリが表示されています。エラーが返される可能性があるため、二重ダッシュを削除することにします。

二重ダッシュを空白に置き換えます。

[OK] をクリックすると、[金額] 列のすべてのエントリが正しい形式になります。

データ型のアイコンをクリックして、[整数] を選択できるようになりました。

Power Query エディターを使用して日付形式を変更する
LuckyTemplates 内で DAX を使用して値を結合する 3 つの方法
LuckyTemplates の高度な変換

結論

前に述べたように、区切り文字で区切られたデータからさまざまな方法で値を抽出できます。しかし私にとって、このアプローチは最も簡単で最速の方法の 1 つです。

コミュニティのメンバーの 1 人から得たものなので、これはがいかに重要であるかを示しています。それぞれの問題をさまざまな視点から見ることができます。そこから、自分にとって最適だと思われるソリューションを選択するだけです。

ではごきげんよう、

メリッサ

***** LuckyTemplates を学習していますか? *****