パワークエリでデータクレンジング: 文字列結合、0埋め、テキスト関数をマスター(5/11)

1. データタブの[クエリと接続]を選択
2. [データの取得]を選択
3. [その他のデータソースから]を選択
4. [空のクエリ]を選択

数式バーに[= #shared]を入力します。関数の一覧が表示されます。

全ての関数が出てくるので、見やすくする為にテキスト関数に絞ります。関数名（Name列)の右上の▽を選択します。

テキスト関数は、[Text.] が頭に付くので[Text.]と入力します。

関数の説明を確認するには、対象の関数名がある行のFunctionの右側をクリックすると閲覧できます。

最後に関数の動作を検証する方法です。Functionをクリックすると、実際に関数を適用するテキスト型データとその引数を入れることで関数の動作検証をすることができます。

関数とその結果を確認することができます。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

トリミング機能を利用して半角全角に関わらず余分なスペースを取り除きます。

1. トリミング対象の列を選択します。
2. [変換]タブを選択します。
3. [書式]を選択します。
4. [トリミング]をクリックします。

文字の前後に存在している余分なスペースが削除されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

クリーン機能を利用して余分な改行を取り除きます。

1. 改行を取り除く対象の列を選択します。
2. [変換]タブを選択します。
3. [書式]を選択します。
4. [クリーン]をクリックします。

余分な改行が削除されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

パワークエリエディタで表示すると、以下のように先頭行にのみ値が入り、後はnull表記となります。データベース形式で考えてた場合、nullだと何のことか分からないので修正します。

1. 対象列を選択して[変換]タブを選択します。
2. [フィル]から[下]を選択します。

以下のように、nullが先頭行で埋められました。

エクセルに読み込むと以下のようになります。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [抽出]から[最初の文字]をクリックします。

頭文字から抽出したい文字数を指定します。以下では、２を指定しています。

2と指定したので、頭文字から数えて２文字が抽出されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [抽出]から[最後の文字]をクリックします。

最後の文字から抽出したい文字数を指定します。以下では、３を指定しています。

3と指定したので、最後の文字から数えて3文字が抽出されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [抽出]から[範囲]をクリックします。

以下では、3文字目から３文字を抽出するように指定しています。開始インデックスは０から始まります。

3文字目から３文字が抽出されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [抽出]から[区切り記号の前のテキスト]をクリックします。

区切り記号を指定します。以下では、＠を指定しています。

＠と指定したので、＠より前のテキストが抽出されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [抽出]から[区切り記号の後のテキスト]をクリックします。

区切り記号を指定します。以下では、＠を指定しています。

＠と指定したので、＠より後のテキストが抽出されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [抽出]から[区切り記号の間のテキスト]をクリックします。

区切りの開始と終了を指定します。指定した間のテキストが抽出されます。

[＠]と[.]間のテキストが抽出されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [書式]から[小文字]をクリックします。

大文字小文字が混在していたが、全て小文字に変換されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [書式]から[大文字]をクリックします。

大文字小文字が混在していたが、全て小文字に変換されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. 対象の列を選択して[列の追加]を選択します。
2. [書式]から[各単語の先頭文字を大文字にする]をクリックします。

大文字小文字が混在していたが、全て小文字に変換されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

数値型になっていると０埋めができないので、テキスト型に変換します。

1. 列名の左側の[1.2.3]を選択
2. メニューから[テキスト]を選択

1. [列の追加]タブを選択
2. [カスタム列]をクリック

Text.PadStart関数を入力します。以下の例では、先頭８桁を０埋めするという意味です。

Text.PadStart([0埋め],8,”0″)

全ての行が８桁となり、左側が０埋めになっています。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

日付型になっていないと関数が利用できないので、日付型に変換します。

列名の左側のアイコンを選択

メニューから[日付]を選択します。[日付/時刻]でも大丈夫な気がしますが、関数が使えません。

1. [列の追加]タブを選択
2. [カスタム列]をクリック

Date.ToText関数を入力します。以下の例では、20221215のような表記に変換しています。

Date.ToText([日付],”yyyyMMdd”)

指定した表記に変換されました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. [列の追加]タブを選択
2. [カスタム列]をクリック

Text.Format関数を入力します。以下の例で説明すると、#{0}#{1}#{2}が後半で定義している列名の順番です。[名前],[身長],[体重]が列名です。（使用できる列から挿入することが可能です。）

入力例：Text.Format(“#{0}の身長は、#{1}CMで体重は#{2}です。”,{[名前],[身長],[体重]})

定義した文章が作成されたました。

パワークエリエディタを開きます。

1. テーブル内のセルを選択して[データ]タブを選択します。
2. [テーブルまたは範囲から]を選択します。

1. [変換]タブを選択
2. [値の置換]をクリック

[power Query]を[パワークエリ]に置換します。

置換されました。

置換する値に全角スペースを入れ、置換後は値なしにします。

スペースが消えました。

最後に置換で余分な記号[！]を削除します。

置換する値に[！]を入れ、置換後は値なしにします。

[]が消えました。