【DAX関数】文字列の抽出(LEFT、FIND、LEN、SUBSTITUTE関数の使い方)

PowerBI DAX 抽出 LEFT関数 LEN FIND SUBSTITUTE RIGHT関数 MID 文字列抽出DAX関数
スポンサーリンク

PowerBIで扱うデータにはハイフン「-」やコンマ「,」などで区切られたデータもあり、データ列から指定した文字のみを抽出したい時ってありますよね。本日は、そんな場合にDAX関数のLEFT、RIGHT、MID関数を用いた基本的な抽出方法や、LEN、FIND、SUBSTITUTE関数と組み合わせた応用的な方法を紹介します。

【本記事でできること】
・LEFT、MID、RIGHT、LEN関数の使い方
・FIND関数、SUBSTITUTE関数の使い方
・LEFT関数とFIND関数を組み合わて指定した位置までの文字の抽出
・SUBSTITUTE関数、FIND関数を組み合わせて右から文字を探して取得

DAX関数を使わず、PowerBIやExcelのパワークエリの分割、抽出機能を用いた方法は下記で説明していますので、合わせてご参考ください。
▶パワークエリで列の分割、結合

DAX関数を用いた文字の抽出方法

DAX関数を用いて文字を抽出するためには、
・LEFT関数、MID関数、RIGHT関数
・LEN関数、FIND関数
・SUBSTITUTE関数
を理解して組み合わせることで、ほぼ全ての状況で抽出が可能です!

【LEFT関数】左から文字数を指定して抽出
=LEFT(データ列,抽出したい文字数)

▶Microsoftリンク LEFT関数

【RIGHT関数】右から文字数を指定して抽出
=RIGHT(データ列,抽出したい文字数)

▶Microsoftリンク RIGHT関数

【MID関数】指定した位置から文字数を指定して抽出
=MID(データ列,抽出したい先頭文字の位置,抽出したい文字数)
例えば、MID(データ列,3,2)で、3文字目から2文字抽出する指示になります。

▶Microsoftリンク MID関数

【LEN関数】文字列の長さ(文字数)を取得
=LEN(データ列)

▶Microsoftリンク LEN関数

【FIND関数】検索する文字までの、文字数を取得
=FIND(”①検索文字” , ②データ列,③,④)
③:検索を開始する文字数
④:検索文字が見つからない場合に返す値
③④は省略可能で、今回も使いません。
例えば、スラッシュ「/」までの文字数を取得する場合は、
=FIND(”/” , ’データ列)

▶Microsoftリンク FIND関数

【SUBSTITUTE関数】指定の文字を変換
=SUBSTITUTE(データ列,変換前の文字,変換後の文字,変換する番号)
例えば、2個目のスラッシュ「/」を「▲」に変換する場合は、
=SUBSTITUTE(‘データ列 , ”/” , ”▲” , 2)

▶Microsoftリンク SUBSTITUTE関数

今回はこれらのDAX関数を用いて、
・フルネームから苗字、名前の抽出
・製品データの右にある色のみを抽出
を具体的に解説していきます。

◆フルネームから苗字、名前の抽出

PowerBI DAX LEFT FIND LEN関数 苗字 名前 抽出

◆製品文字列の右から任意の文字を抽出

PowerBI DAX関数 SUBSTITUTE関数 FIND関数 LEN関数 右から文字を探す 位置取得 文字列抽出

【関連記事】
▶【PowerBI】FORMAT関数で日付データから年/月/日/曜日を抽出
▶【DAX関数】DATEADD関数で前年/前月/前日比の取得(日付の引き算)
▶【DAX関数】DATEDIFF関数で日付間の差分を取得(在庫管理表の作成)

【結論】
PowerBIでDAX関数を用いた文字列の抽出は、LEFT、MID、RIGHT関数とLEN、FIND、SUBSTITUTE関数を理解して組み合わせる!

スポンサーリンク

苗字、名前の抽出(LEFT関数、FIND関数、LEN関数)

それでは、DAX関数でフルネームから苗字と名前を抽出していきます。

PowerBIに取り込んでいるデータは下記のフルネームです。苗字と名前の間は全角スペースで区切られています。

PowerBI 取り込みデータ フルネーム 

苗字も名前も、2文字や3文字と抽出したい文字数が変化するので、FIND関数やLEN関数と組み合わせる必要があります。

・苗字の抽出:LEFT関数+FIND関数
・名前の抽出:RIGHT関数+FIND関数+LEN関数

それぞれの関数を組み合わせて、指定の文字を抽出していきます!

スポンサーリンク

DAX関数で苗字の抽出(LEFT関数、FIND関数)

苗字の抽出はLEFT関数とFIND関数を組み合わせることで取得できます。FIND関数で全角スペースまでの文字数を取得し、LEFT関数に入れ込めばOKです!

◆使用するDAX関数
【LEFT関数】左から文字数を指定して抽出
=LEFT(データ列,抽出したい文字数)

【FIND関数】検索する文字までの、文字数を取得
=FIND(”検索文字” , データ列)

①FIND関数で全角スペースまでの文字数を取得
②LEFT関数に入れ込む

苗字 = LEFT('フルネーム'[フルネーム],FIND(" ",'フルネーム'[フルネーム]))

・データ列:フルネーム'[フルネーム]
 ※今回はフルネームというExcelデータで列名もフルネーム
・検索文字:全角スペースは” ”

PowerBI DAX 苗字抽出 文字 LEFT関数 FIND関数

フルネームから苗字を抽出することができました!

苗字の抽出 DAX関数 PowerBI FIND関数 LEFT関数

【結論】
苗字の取得はFIND関数で指定した文字までの文字数を取得し、LEFT関数に入れ込む!

スポンサーリンク

DAX関数で名前の抽出(RIGHT関数、FIND関数、LEN関数)

続いて、名前の抽出は右から文字を取得するRIGHT関数と先ほどのFIND関数、全文字数を取得できるLEN関数を組み合わせることで可能です。文字の右から文字数を取得したいので、LEN関数で全ての文字数を取得し、全角スペースまでの文字数を引けばOKです!

◆使用するDAX関数
【RIGHT関数】右から文字数を指定して抽出
=RIGHT(データ列,抽出したい文字数)

【FIND関数】検索する文字までの、文字数を取得
=FIND(”検索文字” , データ列)

【LEN関数】文字列の長さ(文字数)を取得
=LEN(データ列)

①LEN関数で全文字数を取得
②FIND関数で全角スペースまでの文字数取得
③(①-②)をRIGHT関数に入れ込む

名前 = RIGHT('フルネーム'[フルネーム],LEN('フルネーム'[フルネーム])-FIND(" ",'フルネーム'[フルネーム]))
FIND関数 LEN関数 RIGHT関数 PowerBI DAX関数

フルネームから名前を抽出することができました!

PowerBI DAX関数 名前抽出 文字 FIND LEN関数 RIGHT関数

【結論】
名前の取得は
LEN関数で全文字数を取得
FIND関数で指定文字までを文字数を取得
(①-②)RIGHT関数に入れ込む!

スポンサーリンク

右から文字を検索し抽出(SUBSTITUTE関数、FIND関数)

次に応用編として、製品文字列から右にある色のみを抽出する方法を紹介します。使用するデータは、製品、サイズ、色がスラッシュ「/」の区切り記号で区切られた文字列データになります。

PowerBI 使用するデータ 抽出

先ほどと異なり、同じ区切り記号「/」が複数あり、しかも右から検索したいので、なかなか困ります2個目の「/」以降の文字を抽出したい!という指示をする必要があります。

FIND関数で右(後ろ)から特定の文字の位置を取得し、抽出します。SUBSTITUTE関数やFIND関数を組み合わせるので、一見複雑ですが、下記の手順に分解して考えると簡単です。

①区切り記号の数を取得(LEN、SUBSTITUTE関数)
②右にある「/」のみを「▲」に変換(SUBSTITUTE関数)
③変換した「▲」の位置を取得(FIND、LEN関数)
④RIGHT関数で任意の文字を取得(RIGHT関数)

それぞれ、詳しく解説していきます。

スポンサーリンク

①区切り記号の数を取得(LEN関数、SUBSTITUTE関数)

まずは、文字列の中に区切り記号「/」が何個あるかを計算します。「/」が何個あっても、最後の「/」から右の文字数を取得させたいためです。

◆使用するDAX関数
【LEN関数】文字列の長さ(文字数)を取得
=LEN(データ列)

【SUBSTITUTE関数】指定の文字を変換
=SUBSTITUTE(データ列,変換前の文字,変換後の文字,変換する番号)
※全て変換したい場合は最後の変換する番号は不要

手順は、SUBSTITUTE関数で区切り記号「/」が無い文字列を作成して、LEN関数で取得した全文字数から引けばOKです!

①SUBSTITUTE関数で「/」を「””」に変換して無くす
→シャツ/M/red → シャツMred と変換!
 今回は全ての「/」を変換したいので、変換する番号は省略
②LEN関数で「/」を無くした文字数を取得
→シャツMredの文字数をLEN関数で取得
③LEN関数で全文字数を取得
④全文字数から「/」を無くした文字数を引く

区切り数 = LEN('製品'[製品/サイズ/色])-LEN(SUBSTITUTE('製品'[製品/サイズ/色],"/",""))
PowerBI DAX LEN関数 SUBSTITUTE関数 区切り記号 数える

製品/サイズ/色の文字列データから、区切り記号が何個あるか取得することができました!今回は区切り記号が全て2つでしたが、3つや4つなど混在している文字列でも適用可能です。

【結論】
SUBSTITUTE関数で区切り記号が無い状態の文字数を取得し、全文字数から引けば区切り記号の数を取得できる

スポンサーリンク

②右にある区切り記号のみを変換(SUBSTITUTE関数)

区切り記号の数が分かったので、2つ目の区切り記号「/」のみを「▲」に変えます。 これは、数えたい位置を任意の文字「▲」に変換して、▲まで文字数を取得するためです。

◆使用するDAX関数
【SUBSTITUTE関数】指定の文字を変換
=SUBSTITUTE(データ列,変換前の文字,変換後の文字,変換する番号)

SUBSTITUTE関数で右(後ろ)にある区切り記号「/」のみを「▲」に変えればOKです。
=SUBSTITUTE(データ列 , ”/” , “▲” ,2)
をすれば、2個目の「/」を「▲」に変換する指示になります。2は先ほど、区切り記号の数を取得したので、そちらのデータ列を入れればOKです。

▲に変換 = SUBSTITUTE('製品'[製品/サイズ/色],"/","▲",'製品'[区切り数])
PowerBI DAX SUBSTITUTE関数 変換 右から検索 位置

右(後ろ)にある区切り記号のみを変換することができました!

PowerBI DAX SUBSTITUTE関数 FIND関数 右から 文字 位置 検索 変換

【結論】
SUBSTITUTE関数で、右にある区切り記号のみを任意の文字に変換する

スポンサーリンク

③変換した記号の位置を取得(FIND関数、LEN関数)

あとは、FIND関数を用いて、この「▲」の位置を探して取得させればOKです。

◆使用するDAX関数
【FIND関数】検索する文字までの、文字数を取得
=FIND(”検索文字” , データ列)

【LEN関数】文字列の長さ(文字数)を取得
=LEN(データ列)

先ほどと同様にLEN関数で全文字数を取得してFIND関数で「▲」までの文字数を引けば、右からの文字数が取得可能です。

①LEN関数で全文字数を取得
②FIND関数で「▲」までの文字数を取得
③全文字数から「▲」までの文字数を引く

▲の位置を取得 = LEN('製品'[製品/サイズ/色])-FIND("▲",'製品'[▲に変換])
PowerBI LEN関数 FIND関数 右の位置取得 抽出

変換した右(後ろ)の「▲」までの文字数を取得できました!

PowerBI LEN関数 FIND関数 右の位置取得 抽出 文字数

【結論】
FIND関数で変換した記号までの文字数を取得して全文字数から引く!

スポンサーリンク

④右から文字数を取得(RIGHT関数)

「▲」までの右からの文字数が分かったので、最後にRIGHT関数で文字を取得すれば完了です!

◆使用するDAX関数
【RIGHT関数】右から文字数を指定して抽出
=RIGHT(データ列,抽出したい文字数)

RIGHT関数で先ほど、取得した文字数のデータ列を入れます。

色 = RIGHT('製品'[製品/サイズ/色],'製品'[▲の位置を取得])
PowerBI DAX関数 RIGHT関数 右から 位置 抽出 文字列 LEN

これで、右にある特定の文字を検索して抽出することができました!  

【結論】
RIGHT関数で右から指定した文字を取得!

スポンサーリンク

DAX関数で文字列の抽出まとめ

今回のDAX関数を用いた文字列の抽出方法のポイントは下記の4点です。
・LEFT関数とFIND関数を組み合わせれば、
 指定した位置までの文字の抽出が可能
・LEN関数で全文字数を取得できる
・SUBSTITUTE関数で文字の変換ができる
・SUBSTITUTE関数、FIND関数を組み合わせれば、
 右から文字を探して取得することも可能

何かの法則に従って文字を抽出したいときは、今回のLEFT関数やFIND関数、LEN関数、SUBSTITUTE関数などを組み合わせれば対応できます!組み合わせると複雑に見えますが、各々のDAX関数はシンプルですので、ぜひご参考にしてください!

下記でPowerBIの便利機能、DAX関数をまとめています。ぜひご参考下さい!

▶PowerBI、PowerQueryの便利機能、学習方法まとめ

よく使うDAX関数まとめ

良ければ、クリック応援をよろしくお願いします!

にほんブログ村 サラリーマン日記ブログ IT系サラリーマンへ
にほんブログ村

コメント

タイトルとURLをコピーしました