見出し画像

Power Queryで、Webサイト上のスペースで整えられたテキストファイルをデータクレンジングしてみたハナシ

まる🍑

面白いネタをいただいたので、やってみましたｗ

対象サイトはこちら。

「テキストデータダウンロード」…(｡ ･ω･))ﾌﾑﾌﾑ

スペースで整えられている！🤣🤣

Hiro_21さんも無事に解決できましたが、

面白かったので、自分のやり方をメモしておきますｗ

① ExcelからWebサイトにアクセス

空っぽのExcelファイルを用意します。
データメニュー→Webから をクリックして、URLにみずほ銀行のサイトから対象の日のURLをペーストします。

そのまま「データの変換」。

② 不要な行を削除

おー まとまってますね～ｗ

とりあえず上と下にある不要な行を削除します。

上3行がいらないので、行の削除→上位の行の削除

3 を入力して「OK」。

下の20行がいらないので、行の削除→下位の行の削除

20 を入力して「OK」。
うん、すっきり✨

③ 文字を区切って行く

今からやる方法は、文字数が大きく変わらないことが前提のやり方です🙏

文字と文字の間のスペース…見た目を整えるために入れられているので、数が違うし 半角と全角がありますｗ

なので今回は、文字数で区切ることにしてみました。

まずはわかりやすく、通貨から…
一番長い名前は「パプアニューギニアキナ」の11文字です。

列の分割→文字数による分割

「できるだけ左側で1回」(なんじゃそりゃ) で、文字数11を指定してみました。

いい感じ！✨

次はお尻から区切っていきます。

2列目を選択して、

列の分割→文字数による分割で、今回は「できるだけ右側で1回」で6文字。
TTBの小数点を含めた文字数の最大値。

いいね～👏✨

次のTTBというものも同じように…

あれーーー！区切れない😳 そっか、スペースが入ってるからか…

適用したステップの今行った作業のところを選択して、

この数字が文字数っぽい。

適当にいじってみたら、13文字が良さそう！

では、TTSも同じように…

項目別にはできた！！

④ 不要なものの削除

一番後ろの空列は不要なので、右クリックして削除。

すべての列を範囲選択して、「値の置換」。
検索する値には、半角スペースを入力。

全角スペースも入っているのでｗ、

全角スペースも値の置換。

これでデータとしては綺麗な形に✨

⑤ ヘッダーの設定

最後に、「1行目をヘッダーとして使用」。

これで完成✨

⑥ 閉じて読み込む

最後に、「閉じて読み込む」。

できたーーー

⑦ データソースを変更するときは

※後日談あり

Power Queryエディタ開かなくても なんとかできないかな？と思ったけど、無理っぽい？🤔

クエリをダブルクリックして、Power Queryエディタを開く。

適用したステップの「ソース」を選択して…

ここの日付を書き換えて、EnterをすればOK🙆‍♀️

データか書き換わったことを確認したら、「閉じて読み込む」。

ちゃんと書き換わりました！

※後日談：2022/12/24追記

日付が変わってダウンロードしたい対象日が変わったとき、「ドリルダウン」とやらを使えばソースのURLの日付を変更できることを教えていただきました😍

(多分)同じように やったつもりなのですが、うまくできず…😢

こちらのサイトを参考に、URLの日付だけを生成するのではなくて URLすべてを生成するようにしたら うまくできました！

こんな感じで、入力した日付から URLを生成するように関数を入れておき、テーブル名を付けておきます(私は「URL」と付けました)。

このテーブルを読み込み、

URLのセル？で右クリックして、ドリルダウン。

こんな状態になります。

この状態で、ダウンロードデータの方の適用したステップのソースのURLをごそっと変更！

意気揚々とEnterすると… (ﾉﾟ⊿ﾟ)ﾉｱｳｯ!! エラー！！

ありがたいことに、上で紹介したページに解決方法が載ってました…😭
ありがとうございます ありがとうございます🙏✨

ファイル→オプションと設定→クエリのオプションから、プライバシーのレベルを「常にプライバシーレベル設定を無視します」に変更。

プレビューの更新をすると…

出た！！😍

対象日が常に実行日の前日なら、TODAY()-1 にしておけば おっけー🙆‍♀️

対象日が変更になったら、「すべて更新」すればデータが書き換わります🎵

すごい！ドリルダウンすごい！！😍

⑧ 所感

上でも書きましたが、今回のこのやり方は 文字数指定で行うため、文字数が大きく変わらないことが前提のやり方です。

今回の操作対象は、大きく変動することはなさそうなデータなので大丈夫だと思いますが、多少変わっても Power Queryならすぐ修正できるので楽ですね🎵

やば～、Power Query やば～🤤
「テキストファイル」でも、スペースで見た目を整えることがあるんですね🤣
いやはや、勉強になりました。面白かった！！

事務員が少しの背伸びでできる効率化を目指す🌈✨ 自分の好きなものを、楽しく発信していきたいです！ いただいたサポートは学習費にあてさせていただきます🥰