スポンサーリンク

【PowerQuery】関数だと簡単にできるのにパワークエリではどうやるんだ?を集めた①文字列編

パワークエリ
スポンサーリンク
※当サイトは広告を含みます

1.はじめに

パワークエリ1年生の苦悩シリーズ。

エクセル関数だとチャッとできるのに、パワークエリだとどうやるんだ?の、(自分が)よく使うものを集めました。悲しいくらいに忘れるので、毎回どうやるんだっけ?→調べる、と時間がかかるので、ここに書いておくことにしました。

なお、クリック操作でやる方法ではなく、関数を書くほうが好みなので関数でやる方法を主に書いています。

ちなみになぜ関数で書くほうを好むかというと
・関数を知っていると、クリック操作でやるよりステップ数を短くできる場合がある
・関数を知っていると、複雑な要求もパワークエリ内で処理できる場合がある
・クリック操作でやるよりなんかデキル人っぽい感じがする
ような気がする、からです。

文字列関連、型変換関連のM関数はこちらにリストがありました。
Microsoft learn PowerQuery 文字列関数
Microsoft learn PowerQuery 型変換
ここをさがすといい感じのがある。

2.文字列取り出し

エクセル関数だと、LEFT,MID,RIGHT でちゃっとできるのに、パワークエリだとどうやるんだっけがいつもすぐ出てこない。

(1)Left 左から●文字取り出し

Text.Start

あいうえお から あいう を取り出し
Text.Start(“あいうえお”, 3)
→ あいう

ステップの中で使うイメージ

①列の追加→カスタム列
②Text.Start([列],取り出したい文字数)
③左から指定した文字列を取り出した値が入った列が追加される

型を文字列に指定したいときは、4つ目の引数に文字列型(type text)を指定します。
= Table.AddColumn(変更された型, “左取り出し”, each Text.Start([文字列],3), type text)

列の追加ではなく今の列の内容を置き換えたいときは Table.AddColumn ではなく Table.TransformColumns を使用します。

= Table.TransformColumns(変更された型, {{“文字列”, each Text.Start(_, 3), type text}})

Table.TransformColumns を入力するのが面倒なので、これはクリック操作でやったほうが早い。
列を追加する場合も、カスタム列ではなく「列の追加」→「抽出」→「最初の文字」でやってもいい。そのほうが早い。。。

なお、関数の中のアンダーバー each Text.Start(_, 3) は置き換え対象の文字列を持ち込んでいるイメージです。変換前の「あいうえおABC12345」などの各行の置換対象の値がアンダーバーにはいっている・・・難しいそうなのでとりあえず、そのような理解をしています。。。

ときおり出てくる “each” キーワードと “_” (アンダースコア) とは

M言語に慣れる_8回目~EACHを使いこなしてM関数作成~

(2)Right 右から●文字取り出し

Text.End

あいうえお から えお を取り出し
Text.End(“あいうえお”, 2)
→ えお

カスタム列で使用するイメージ
= Table.AddColumn(変更された型, “左取り出し”, each Text.End([文字列],2), type text)

列の値置き換えで使用
= Table.TransformColumns(変更された型, {{“文字列”, each Text.End(_, 2), type text}})

(3)Mid 指定した位置から●文字取り出し

Text.Middle

あいうえお12345 から 123 を取り出し
Text.Middle(“あいうえお12345”, 5, 3)
→ 123

注意すべきは開始位置を指定する数値(●文字目から取り出し)は1文字目を0からカウントします。
6文字目から取り出したいなあ、と思うときは「5」と指定します。

カスタム列で使用するイメージ
= Table.AddColumn(変更された型, “テキスト範囲”, each Text.Middle([文字列], 5, 3), type text)

列の値置き換えで使用
= Table.TransformColumns(変更された型, {{“文字列”, each Text.Middle(_, 5, 3), type text}})

クリック操作でやる場合は 「列の追加」(または「変換」)→「抽出」→「範囲」とします。

似たような関数で Text.Range があるが、Middle で事足りるのではないか、と思いますので割愛。

3.その他文字列関連

(1)LEN 文字列の長さ

Text.Length

Text.Length(“あいうえお”)
→ 5

(2)Trim 空白削除

Text.Trim

先頭と末尾の空白を削除してくれる

= Text.Trim(”  あいうえお  ”)
→あいうえお

カスタム列で使用
= Table.AddColumn(ソース, “トリミング”, each Text.Trim([文字列]), type text)

変換
= Table.TransformColumns(ソース,{{“文字列”, Text.Trim, type text}})

クリック操作では 「列の追加」(または「変換」)→「書式」→「トリミング」になります。

先ほどのLENと組み合わせていっぺんに使いたいとき

= Table.AddColumn(ソース, “トリミング”, each Text.Length(Text.Trim([文字列])), Int64.Type)

(3)先頭を0で埋める

00123,01234,12345等と桁数をそろえて頭をゼロで埋めたいとき

普通のエクセル関数だとTEXTを使ったりしますね。
=TEXT(123,”00000″)
→”00123”

①数値から

Number.ToText

= Number.ToText(123, “D5”)
→”00123″

②文字列から

Text.PadStart

= Text.PadStart(“123”, 5, “0”)
→”00123″

クリック操作でやるにはこちらの記事がわかりやすかったです
PowerQuery 数値の桁数を揃える方法(0埋め)

PadStart がどうしても覚えられなかったのですが、Pad→パッド→肩パッド→詰め物 というイメージで覚えました。Padding は「詰め物、水増し」という意味があるようです。頭をゼロで埋めて桁それ終えすることをゼロパディングというようです。
Weblio 辞書 > 辞書・百科事典 > デジタル大辞泉 > ゼロパディングの意味・解説

(4)置換

Text.Replace

= Text.Replace(“きらぼしBK”, “BK”, “銀行”)
→”きらぼし銀行”

クリック操作でやる場合はこちらの記事がわかりやすかったです。
PowerQuery 文字列の置換|完全一致・部分一致・複数条件

4.型変換

(1)数値を文字列に変換

Number.ToText

↓こちらの記事が大変わかりやすかったです。
Power Query の Number.ToText 関数

第二引数でフォーマットも指定できるところが重宝。
引数が文字列型じゃなきゃダメ!という関数の入れ子として利用することもあり。

= Number.ToText(1234)
→ “1234”

= Number.ToText(0.1234, “p1”)
→”12.3%”

= Number.ToText(1234567, “000-0000”)
→”123-4567″

頭をゼロ埋めしてくれた素敵

関数の入れ子として利用する例

単純に 数値と文字列をくっつけたいとき
“ID” & [数値の列]
だとエラーになる
“ID” & Number.ToText([数値の列])
と、テキストに変換してからだと怒られない。
パワークエリ、型にうるさい。

上の図の例ではついでにフォーマット”D6″として桁数をそろえています。

(2)文字列を数値に変換

Number.FromText

= Number.FromText(“1234”)
→ 1234

5.参考文献、記事

Microsoft Learn より

Microsoft learn PowerQuery 文字列関数

Microsoft learn PowerQuery 型変換

Text.Start
Text.End
Text.Middle
Text.Length
Text.Trim
Number.ToText
Number.FromText
Text.PadStart
Text.Replace

参考記事 ありがとうございました

PowerQuery 数値の桁数を揃える方法(0埋め)

Weblio 辞書 > 辞書・百科事典 > デジタル大辞泉 > ゼロパディングの意味・解説

Power Query の Number.ToText 関数

PowerQuery 文字列の置換|完全一致・部分一致・複数条件

ときおり出てくる “each” キーワードと “_” (アンダースコア) とは

M言語に慣れる_8回目~EACHを使いこなしてM関数作成~

パワークエリはこの書籍で勉強しました。お勧めです。

コメント

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