【超簡単!】パワークエリで文字列を分割、結合する方法

パワークエリ PowerQuery 列の分割 結合 抽出PowerQuery/Pivot
スポンサーリンク

社内で扱うデータには情報が組み合わさったものも多く、データを分割したい場面ってありますよね。LEFT関数、MID関数などを用いて無理やりデータを分割していましたが、パワークエリの列の分割機能を使えば一瞬です。一気に分割する方法や、結合する方法もあわせて解説していますので、ぜひご参考ください。

【本記事でできること】
・列データを文字数を指定して分割する
・分割したデータを「/]で結合し日付データに変換
・区切り記号をキーに一気に分割、抽出

ExcelのPowerQuery、PowerBIのクエリでも同様の操作が可能です!

LEFT、RIGHT、FIND、LEN関数などのDAX関数を使った文字列の抽出方法は下記で説明していますので、合わせてご参考ください!
▶【DAX関数で文字列の抽出】LEFT、FIND、LEN、SUBSTITUTE関数の使い方

パワークエリで列の分割、結合

ExcelでLEFT、MID、RIGHT関数とLEN関数を用いて苦労してデータの分割を設定した経験があるかと思います。パワークエリの列の分割、抽出機能を使えば超簡単にできます

パワークエリの列の抽出機能には、
・LEFT関数に相当する「最初の文字」
・RIGHT関数に相当する「最後の文字」
・MID関数に相当する「範囲」
などがあります。

また、列の分割機能には、
・文字数による分割
・区切り記号による分割
・数字から数字以外による分割
など様々なデータの分割方法が用意されています。

今回は下記の通り、パワークエリの抽出、分割機能を用いてデータの分割方法を紹介します。

◆文字列データを分割し日付データを取得
ハイフン「‐」で区切られた日付の文字列から、年や月、日を抽出した後、分割した文字をスラッシュ「/」で結合して日付データに変換します。

PowerQuery パワークエリ データ分割 抽出 列の分割 列のマージ 結合

◆製品データを一気に分割
コンマ「,」で区切られた1列の製品データを、製品、サイズ、色の3列に分割します。

PowerQuery パワークエリ 列 分割  カンマ コンマ 区切り記号 データ分割

【関連記事】
【パワークエリ】日付データから年/月/週/曜日、年度を一瞬で抽出する方法
▶【PowerBI】FORMAT関数で日付データを年/月/日/曜日に変換
▶【PowerBI】カレンダーテーブルの自動作成(テンプレコードあり)
▶【DAX関数】DATEADD関数で前年/前月/前日比の取得(日付の引き算)
▶【DAX関数】DATEDIFF関数で日付間の差分を取得(在庫管理表の作成)

【結論】
PowerQueryの列の抽出、分割機能を使えば、簡単に文字数を指定した分割や、区切り記号による分割ができる!

スポンサーリンク

文字数、区切り記号を指定した列の分割、抽出

それでは、パワークエリで文字数を指定し、データを分割していきます。方法は、「列の追加」→「抽出」から設定するだけと超簡単です。

下記の日付文字列のデータをクエリに取り込んでいる状態から具体的に解説していきます。「2023-3-1-A」とハイフンで日付とA勤、B勤が入ったデータになっています。

パワークエリ データ取り込み

このデータを年、月、日、勤務に分割していきます。
まずは、年を抽出します。年は左側から4文字抽出したいので、Excel関数のLEFT関数的な設定をしていきます。
①列の追加タブ
②抽出
③最初の文字
④カウント数「4」でOK

パワークエリ 列の追加 抽出 最初の文字 分割

これで、年を抽出できました。列名は任意に変えればOKです。

パワークエリ 年 抽出 文字 分割

続いて、月を取得します。6文字目から1文字抽出できればよいので、ExcelのMID関数にあたる設定をしていきます。
①範囲
②開始インデックス:開始したい文字数-1を指定
③文字数:取得したい文字数を指定

注意なのが、MID関数と異なり、開始インデックスに指定する数は、開始したい文字数マイナス1した数を指定する点です。つまり、今回は6文字目から取得したいので5を入力しています。

パワークエリ 範囲 MID関数 分割 抽出 開始インデックス

日付の文字列から月を取得することができました!

パワークエリ 文字分割 月取得

しかし、この取得方法では、10月、11月になった場合に文字数が「1」なのでうまく取得できません。LEN関数と組み合わせた力技もできますが、パワークエリにはこの場合も簡単に設定できる機能があります。それは、区切り記号で間の文字を取得する方法です。

同様に「抽出」の中の
①区切り記号の間のテキスト

パワークエリ 区切り記号 抽出 文字分割 PowerQuery 区切り記号の間のテキスト

②開始区切り記号、終了区切り記号を指定
③詳細オプションを開き、「入力の選択から」を選択

PowerQuery 文字抽出 分割 区切り記号 ハイフン

指定した最初のハイフン「-」区切り記号の間の文字だけを取得することができました。こちらの設定ですと、間の文字数が変わっても問題なく文字を取得できます!

日を取得する場合は、同様に区切り記号の間のテキストからスキップする開始区切り記号の数に「1」と設定すればOKです。最初の記号は飛ばして、2つ目の記号から次の記号の間の文字を取得するという指示になっています。

PowerQuery クエリ 区切り記号の間 抽出 分割

日のデータも日付文字列から抽出することができました!

パワークエリ 日付 文字 抽出 分割

最後に、A勤、B勤の勤務はExcelのRIGHT関数にあたる、「最後の文字」を設定しすればOKです。※「区切り記号の後のテキスト」からでも、同様のことは可能です。

PowerQuery RIGHT関数 最後の文字 右から 分割 抽出

これで、日付文字列から分割、抽出することができました。

パワークエリ 日付データ 抽出 分割

抽出機能では、
・LEFT関数に相当する「最初の文字」
・RIGHT関数に相当する「最後の文字」
・MID関数に相当する「範囲」
・区切り記号を使った抽出

を覚えておけば、いろいろな場面で汎用性が高いので、ぜひ使ってみてください!

PowerQuery パワークエリ 抽出 最初の文字 最後の文字 範囲 区切り記号

【結論】
パワークエリの「列の追加」→「抽出」から文字数や区切り記号を指定したデータ抽出ができる!

スポンサーリンク

列の結合(マージ)で日付型への変換

次に、分割した年、月、日のデータを「/」の区切り記号で結合し、日付データに変換していきます。日付型でのデータを作成することができるので、時系列分析に便利です。

作成した年、月、日の列を指定して、
①列の追加
②列のマージ

パワークエリ PowerQuery 列のマージ 結合

列のマージの設定画面が立ち上がるので、どうやって結合させたいかを設定します。
③「/」はデフォルトに無いので、カスタムを選択
④「/」を入力
④列名「日付」を入力

PowerQuery パワークエリ 列のマージ カスタム

データを「/」で結合することができました。下記のように、日付型へ変換させれば完了です!

PowerQuery パワークエリ データ型 変換

ちなみに、結合方法は下記のように、区切り記号が無くてもOKですし、コロンやスペースなども自由に設定した結合ができます。

パワークエリ PowerQuery 列のマージ 区切り記号 結合

【結論】
列の結合は「列のマージ」から区切り記号を選ぶだけ!

スポンサーリンク

区切り記号をキーに一気に列を分割

次に、任意の区切り記号を指定して、一気に分割できる方法を紹介します。

下記のように、製品名、サイズ、色が「,」コンマで区切られたデータを一括で分割していきます。会社の製品データって意外とこういうデータが多く、やっかいですよね…

PowerQuery パワークエリ 取り込みデータ コンマ カンマ 分割 区切り記号

このデータを、「,」コンマを区切り記号に列の分割をしていきます。
①変換タブ
②列の分割
③区切り記号による分割

列の分割 パワークエリ 区切り記号による分割 PowerQuery

④区切り記号を選択:コンマ「,」
⑤区切り記号の出現ごと

列の分割 パワークエリ 区切り記号による分割 PowerQuery コンマ

コンマを区切り記号に、列の分割を指示しています。1つの列データを製品、サイズ、色の3つの列に分割することができました!

パワークエリ 列の分割 抽出 PowerQuery

ちなみに、区切り記号は先ほどと同様、コンマ以外にスラッシュ「/」やスペースなども指定できます。

また、分割方法は今回紹介した「区切り記号による分割」以外に、
・文字数による分割
・位置
・小文字から大文字による分割
・数字から数字以外による分割
などの方法がありますので、ぜひ試してみてください。

Microsoft 区切り記号による列の分割

https://learn.microsoft.com/ja-jp/power-query/split-columns-delimiter

【結論】
「列の分割」→「区切り記号による分割」からキーとなる記号を指定して、列を一気に分割できる!

スポンサーリンク

パワークエリで列の分割、結合まとめ

今回紹介したパワークエリでデータ列を分割するポイントは下記の3点です。
・「列の追加」→「抽出」から文字数を指定しデータ抽出できる
・「列のマージ」から区切り記号を選んで列の結合ができる
・「区切り記号による分割」で一気に分割できる

パワークエリを使えば、Excel関数で一つ一つ指定しなくてもデータ列の分割が非常に簡単にできます。PowerBIのクエリでも同様の設定方法ですので、ぜひご参考にしてください!

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

▶パワークエリ、パワーピポットまとめ

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

よく使うDAX関数まとめ

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

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

コメント

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