[[20220118155644]] 『パワークエリでの郵便番号整形』(やま) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『パワークエリでの郵便番号整形』(やま)

お世話になります。
いつも勉強させていただいております。

表題の件につきまして効率の良い方法があればご教授ください。

現在定期的にCSVからデータ取り込みをして、データファイルを作っており、
データ取り込みにパワークエリを利用しています。

データは郵便番号列を含む個人情報データです。
郵便番号は各々の個人がバラバラで入力したもので、その段階で入力規則などで制御できれば良いのですが、そこは諸事情で難しく、
データ上で整形をしています。

ハイフンの有無(人によってはダッシュ記号だったり)や、全半角、北海道住所だと先頭の0が落ちていたりとごちゃごちゃの状態です。

これをスムーズに整形し半角3桁-半角4桁で定期的に吐き出す作業をしたいとおもっています。

同様の作業をしている方や何か効率の良い方法などご存知の方が居ましたらアドバイスをいただけますでしょうか。

< 使用 Excel:Excel2016、使用 OS:Windows10 >


補記です。
データ方を文字列にして、条件列を追加し、ハイフンがなければ補う?0落ちにはプレフィックスを追加して......とざっくりとイメージはしているのですがあまりうまくいかず。

どうぞよろしくお願いいたします。
(やま) 2022/01/18(火) 16:06


PowerQueryじゃないとだめですか?
=TEXT(SUBSTITUTE(ASC(A1),"-",""),"000-0000")
とかの関数ではだめですか
(え) 2022/01/18(火) 17:01

え様

ご返信ありがとうございます。
はい、できればパワークエリでと考えています。
吐き出されたデータ、テーブルは運用上行列追加ができないため、その列を参照して整形して......というのを避けたいのです。

(やま) 2022/01/18(火) 17:12


こんな感じでできませんか

1)データ型をテキストに変換
2)テキストをリストに変換
3)数字は半角に変換
4)数字以外の文字は削除
5)ハイフンを追加
6)リストをテキストに結合

(マナ) 2022/01/18(火) 17:42


 当該列のデータを型の変更でテキストにしておき
 0〜9までと考えられる区切りを全て置換して7桁の文字(数字)に変更して
 列の分割の文字数で3桁と4桁に分割しておき
 列のマージで区切りをカスタムでハイフンにしてマージするとかはどうでしょうか
(なるへそ) 2022/01/18(火) 17:48

Power Query で 全角数字 から 半角数字 に変換するには
https://qiita.com/PowerBIxyz/items/e128e874fe47957d1dfd
(参考) 2022/01/19(水) 10:45

理解しやすいように、列を追加して、変換前も残しています。

 let
    ソース = Csv.Document(File.Contents("ファイルパス"),[Delimiter=",", Encoding=932, QuoteStyle=QuoteStyle.None]),
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
    変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"郵便番号", type text}}),
    追加されたカスタム = Table.AddColumn(変更された型, "リスト化", each Text.ToList([郵便番号])),
    追加されたカスタム1 = Table.AddColumn(追加されたカスタム, "半角", each List.ReplaceMatchingItems([リスト化], List.Zip({{"0".."9"},{"0".."9"}}))),
    追加されたカスタム2 = Table.AddColumn(追加されたカスタム1, "数字のみ", each List.RemoveItems([半角], List.RemoveItems([半角],{"0".."9"}))),
    追加されたカスタム3 = Table.AddColumn(追加されたカスタム2, "ハイフン挿入", each List.InsertRange([数字のみ],3,{"-"})),
    追加されたカスタム4 = Table.AddColumn(追加されたカスタム3, "文字連結", each Text.Combine([ハイフン挿入]))
 in
    追加されたカスタム4

 '-------

 >4)数字以外の文字は削除
 こんな感じでできるかと思いましたが、できませんでした。
 List.Select([半角],{"0".."9"})

(マナ) 2022/01/19(水) 20:15


 >>4)数字以外の文字は削除
 >こんな感じでできるかと思いましたが、できませんでした。

 List.Selectの2番目の引数は関数なので、
https://docs.microsoft.com/ja-jp/powerquery-m/list-select

 List.Select([半角], each List.Contains({"0".."9"} , _ ))

 みたいになるのではないでしょうか
(´・ω・`) 2022/01/19(水) 21:09

ありがとうございます。
List.Conteinsも試していたのですが
使い方悪く、エラー連発で、諦めていました。

(マナ) 2022/01/19(水) 21:37


 昨日寝る前にふと、数字のみにするのは、最初に
 Text.Select([郵便番号],{"0".."9","0".."9"})
 とするのがいいかもしれないなとおもったので、やってみました
 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"郵便番号", type text}}),
    filterdigit=(s)=>Text.Select(s,{"0".."9","0".."9"}),
    zenhandigit=(s)=>Text.Combine(List.ReplaceMatchingItems(Text.ToList(s), List.Zip({{"0".."9"},{"0".."9"}}))),
    formatzip=(s)=>Text.Insert(Text.End("0000000" & s,7),3,"-"),
    追加されたカスタム=Table.AddColumn(変更された型,"カスタム", each formatzip(zenhandigit(filterdigit([郵便番号]))))
 in
    追加されたカスタム
(´・ω・`) 2022/01/20(木) 06:45

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.