※以下この記事では「区切り文字で区切られた文字列の各要素」のことを単に「部分文字列」と呼んでいます。
DAXで部分文字列を操作したい
…という場面は結構あると思います。たとえば人名のフルネームが「田中 太郎」みたいに格納されているとして、それを姓・名に分けたい、とか、キー列を作るために列A,列B,列Cをカンマ区切りとかスペース区切りとかで結合した列Key = [A] & [B] & [C]を作ったけど、そのKey列を再度A,B,Cに分けたい、など。
PowerQueryなら簡単にできるし、PowerQueryですべきならPowerQueryですればいいんだけど、データモデルをいじっているときに部分文字列操作が必要なこともありますよね。そういう場面では、DAXでやることになります。
部分文字列を操作したいと思って、DAXの文字列関数を探してみるも、そういうことができる関数は見当たりません。
「DAX substring」「DAX subtext」「DAX delimiter」とかでいろいろググってみても、文字列関数のLEN,MID,LEFT,RIGHTなどを使った解決が出てくるばかりで、確かに原理上できることはわかるけど、「そんな面倒くさいことしてまで部分文字列が必要かというと…」と、かけるコストと得られる利益とのバランスで考えると、まぁいいか、となってしまいます。
そんなときには親子関数を使うといいよ、というのが今回の記事です。
文字列関数を使った失敗例
まずは失敗例から。
文字列関数を使って部分文字列操作をする場合、たとえば次のような式を書くことになります。
CountDelimiter =
VAR _delimiter = " "
VAR _text = "田中 マルコ 太郎"
RETURN MAXX(
GENERATESERIES(1,LEN(_text)),
IF(CONTAINSSTRING(_text,REPT("*" & _delimiter,[Value])),[Value])
)
//結果:2
このDAX式を実行すると、デリミタ" "の個数 2 が返ってきます。
一応説明すると、まずGENERATESERIES(1,LEN(_text))によって、単一列Valueを持つテーブルが作られます。列Valueの値は連番で、開始値は1、終了値は_textの文字数LEN(_text)です。イテレータ関数MAXXは、このValue列を「文字列_textの中で_delimiterを区切り文字として使用した回数」とみなしたときの最大値を返します。（CONTAINSSTRINGやSUBSTITUTEなどの一部の文字列関数ではワイルドカード*が使用できます）
あるいは次のようなDAX式を使う方法もあるでしょう。
DelimiterPositions =
VAR _delimiter = " "
VAR _text = "田中 マルコ 太郎"
RETURN CONCATENATEX(
FILTER(
ADDCOLUMNS(
GENERATESERIES(1,LEN(_text)),
"isSpaceChar",
MID(_text,[Value],1)=_delimiter
),
[isSpaceChar]
),
[Value],",",
[Value],ASC
)
//結果: 3,7
３文字目と７文字目が区切り文字だよという結果が得られます。この結果をもとにすれば、部分文字列の開始位置と終了位置がわかるので、部分文字列は一応取得できます。原理上は。
しかしDAX式を書きながら、「ほんとにそんな面倒くさい式を組んでまでやることなのか？」という疑問が湧いてしまい、やる気がなくなってしまいました。
面倒くさいからやりたくないけど、需要は絶対にあるんだから、何かもっと簡単な解決法があるはず…。そう期待しながらもう少しGoogle検索を続けていると、次のページにたどり着きました。
本題に入る前の前段が長すぎたり主張がわかりにくいけど、どうやら親子関数を使うといいよ、と書いてあります。
親子関数とは
親子関数の公式ドキュメント
| 関数 | 説明と戻り値 | 構文 |
|---|---|---|
| PATH | 現在の識別子のすべての親の識別子を含む区切りテキスト文字列を返します。 | PATH(<ID_columnName>, <parent_columnName>) |
| PATHCONTAINS | 指定された path 内に指定された item が存在する場合に TRUE 、それ以外の場合は FALSE を返します。 | PATHCONTAINS(<path>, <item>) |
| PATHITEM | PATH 関数の評価によって得られた文字列から、指定した position にある項目を返します。位置は左から右にカウントされます。 | PATHITEM(<path>, <position>[, <type>]) |
| PATHITEMREVERSE | PATH 関数の評価によって得られた文字列から、指定した position にある項目を返します。 位置は、右から左に向かって逆方向にカウントされます。 | PATHITEMREVERSE(<path>, <position>[, <type>]) |
| PATHLENGTH | 取得した PATH の結果内の指定された項目までの、自身を含めた親の数を返します。 | PATHLENGTH(<path>) |
部分文字列関数としての親子関数
正直公式ドキュメントを読んでもあんまりよくわからない（というかそもそも親子階層の使い方がよくわからない）のですが、あくまで今の主旨は親子階層をコントロールすることではなく部分文字列のコントロールなので、親子関数の本来の使い方については忘れることにします。
親子関数を部分文字列関数として使う場合、次のように解釈します。
（PATH関数は部分文字列関数としては使わないため省略しています。PATH関数は「識別子の階層フルパスから区切りテキスト文字列を取得する関数」です。）
| 関数 | 説明と戻り値 | 構文 |
|---|---|---|
| PATHCONTAINS | 文字列 string を区切り文字 "|" で区切ったとき、部分文字列 substring が存在する場合に TRUE 、それ以外の場合は FALSE を返します。(2023/2/23追記:Case Insensitiveです。大文字小文字を区別しません。) | PATHCONTAINS(<string>, <substring>) |
| PATHITEM | 文字列 string を区切り文字 "|" で区切ったときの、指定した index （開始値 1）の部分文字列を返します。インデックスは左から右にカウントされます。index が範囲外のときは空文字列""が返ります。 | PATHITEM(<string>, <index>) |
| PATHITEMREVERSE | 文字列 string を区切り文字 "|" で区切ったときの、指定した index の部分文字列を返します。インデックスは、右から左に向かって逆方向にカウントされます。index が範囲外のときは空文字列""が返ります。 | PATHITEMREVERSE(<string>, <index>) |
| PATHLENGTH | 文字列 string を区切り文字 "|" で区切ったときの、部分文字列の個数を返します。 | PATHLENGTH(<string>) |
注意事項として、デフォルトの区切り文字には縦棒"|"が使用されます。区切り文字を替えたい場合はSUBSTISUTEとかで置換する必要があります。（でも文字列関数だけでロジック構築する手間に比べたら遥かに容易でしょう。）
使用例
使い方は読めばだいたいわかると思いますが、一応使用例を見ておきましょう。
PATHCONTAINS
PATHCONTAINSは部分文字列として含まれるかどうかを判定します。
// true となる例
PATHCONTAINS("A|BCD|EF|","A")
PATHCONTAINS("A|BCD|EF|","BCD")
PATHCONTAINS("A|BCD|EF|","EF")
PATHCONTAINS("A|BCD|EF|","")
// false となる例
PATHCONTAINS("A|BCD|EF|","DE")
PATHCONTAINS("A|BCD|EF|","BC") // "BCD"は部分文字列だが、"BC"は部分文字列ではない
PATHCONTAINS("A|BCD|EF","") // 空文字列""は部分文字列ではない
PATHITEM
PATHITEMは部分文字列をインデックスを指定して抽出します（PATHITEMREVERSEはインデックスが逆順になるだけなので割愛します）。
// インデックス範囲内で指定した場合。
// 以下は true になります
PATHITEM("A|BCD||EF",1)="A"
PATHITEM("A|BCD||EF",2)="BCD"
PATHITEM("A|BCD||EF",3)=""
// ただしインデックス範囲外で指定した
// 以下も true になります
PATHITEM("A|BCD||EF",0)=""
PATHITEM("A|BCD||EF",-1)=""
PATHITEM("A|BCD||EF",5)=""
PATHITEMおよびPATHITEMREVERSEは、インデックス範囲外を指定して呼び出すと空文字列""が返ります。範囲外を指定しないように気を付けるか、PATHCONTAINS(string,"")で判定しておくといいかもしれません。
PATHLENGTH
PATHLENGTHは部分文字列の個数を取得します。
PATHLENGTH("A|BCD|EF") //結果: 3
PATHLENGTH("|A|BCD||EF|") //結果: 6
応用1: すべての部分文字列からなるテーブルを作成する
応用編です。親子関数を使って、部分文字列すべてからなるテーブルを作成してみます。
SubstringTable =
VAR _string = "A|BCD||EF"
RETURN ADDCOLUMNS(
GENERATESERIES(1,PATHLENGTH(_string)),
"Substring",PATHITEM(_string,[Value])
)
実行すると次のテーブルが返ります。
| Value | Substring |
|---|---|
| 1 | "A" |
| 2 | "BCD" |
| 3 | "" |
| 4 | "EF" |
文字列"A|BCD||EF"からすべての部分文字列を取得できました。
応用2: 特定の部分文字列が何度表れたか数える（2023/2/23追記）
PBIJPの発表用にサンプルファイルを準備していたときに思い付いたのでやってみました。
PATHCONTAINSでは部分文字列が含まれているかどうかしか判断しないので結果はTRUE/FALSEしかわかりませんが、部分文字列が何回出現したかまで知りたいときもあるでしょう。
たとえば次のテーブルKingSpeechで（Sentence列はキング牧師のスピーチを文ごとに行に入れたものです）
| index | Sentence |
|---|---|
| 1 | But there is something that ... |
| 2 | The marvelous new militancy which ... |
| 3 | We cannot walk alone ... |
| 4 | And as we walk we ... |
| 5 | We cannot turn back ... |
| 6 | There are those who are ... |
| 7 | I am not unmindful that ... |
| 8 | Let us not wallow in the valley of despair I say ... |
| 9 | I have a dream that ... |
| 10 | I have a dream that ... |
| 11 | I have a dream that ... |
| 12 | I have a dream that ... |
Sentenceに"I"もしくは"me"という単語が何回表れたか知りたい、しかもできれば何単語目に現れたかも知りたい、というような状況です。
これは次のDAX式（計算列用）でできました。
Count me =
VAR _Sentence separated by vert = SUBSTITUTE('KingSpeech'[Sentence]," ","|")
VAR _count words = PATHLENGTH(_Sentence separated by Vert)
VAR _CountTable = ADDCOLUMNS(
GENERATESERIES(1, _count words),
"Included", PATHITEM(_Sentence separated by vert,[Value]) in {"I","me"}
)
RETURN COALESCE(COUNTROWS(FILTER(_CountTable,[Included])),0)
where I appear =
VAR _Sentence separated by vert = SUBSTITUTE('KingSpeech'[Sentence]," ","|")
VAR _count words = PATHLENGTH(_Sentence separated by Vert)
VAR _CountTable = ADDCOLUMNS(
GENERATESERIES(1,_count words),
"Included", PATHITEM(_Sentence separated by vert,[Value]) in {"I","me"}
)
RETURN CONCATENATEX(FILTER(_CountTable,[Included]),[Value],",",[Value],ASC)
実行結果
| index | Sentence | Count me | where I appear |
|---|---|---|---|
| 1 | But there is something that I ... | 1 | 6 |
| 2 | The marvelous new militancy which ... | 0 | BLANK |
| 3 | We cannot walk alone ... | 0 | BLANK |
| 4 | And as we walk we ... | 0 | BLANK |
| 5 | We cannot turn back ... | 0 | BLANK |
| 6 | There are those who are ... | 0 | BLANK |
| 7 | I am not unmindful that ... | 1 | 1 |
| 8 | Let us not wallow in the valley of despair I say... | 2 | 10,29 |
| 9 | I have a dream that ... | 1 | 1 |
| 10 | I have a dream that ... | 1 | 1 |
| 11 | I have a dream that ... | 1 | 1 |
| 12 | I have a dream that ... | 1 | 1 |
ちなみに親子関数を使わなくても、通常の文字列関数を使ってほぼ同様のことができます。実はそれが、最初に失敗例として紹介した、ワイルドカード*をREPTで繰り返して作ったパターンにCONTAINSSTRINGでワイルドカードマッチングさせる方法です。
ですが今回の親子関数を使った方法はその完全な上位互換と言えます。CONTAINSSTRINGはワイルドカードを使ったパターンでしか指定できない（なので今の例でやったように"I"と"me"のどちらかにマッチさせるという指定ができない）からです。正規表現が使えたらいいんですけどね…（Microsoftって正規表現のこと嫌ってるんですかね？）。
おわりに
親子関数が何なのかはまだわかっていませんが、関数を本来の用途と違う用途で使うことでまさかこんなに大活躍することがあるとは驚きですね。