メインカテゴリーを選択しなおす
「Excelパワークエリ データ収集・整形を自由自在にする本」鷹尾 祥 第7章 さらなる活用に向けて 395ページに記載の式のエラー解決法
【Power Query】データソースの管理方法（ファイルの状況と対応例）
Power Query（パワークエリ）を活用する際、データソースをどこに配置し、どのように接続すると効率的かで悩みます。せっかく設定してもデータソースの保存場所や名前変更で、データソースエラーとなり、その都度の設定が必要になったり、部外や社外の人と結果だけを共有したい時はどのようにすればいいかなど。 そこで、これまでのPower Queryを利用してみての備忘録を中心に、ファイルの状況と対応例をまとめてみました。データソースの管理方法についての個人的な考えです。 内容： データソース変更の基本 データソースのファイル名変更が多い（名前に年月名などがあり、バラつきがある） データソースの保存場所…
【Excel】フォーマットは同じで、 「ファイル名」が異なるデータのインポート効率化
Excelの標準機能にあるPowerQuery（パワークエリ）を活用して、フォーマットは同じで、「ファイル名」が異なるデータのインポート業務を効率化する方法を調べた備忘録。 背景は、同一フォーマットで、ファイル名が取得時期や対象によって異なる次のようなファイルで、繰り返し発生する集計業務の効率化。 例） 売上データ_2023年4月.xlsx 売上データ_2023年5月.xlsx 売上データ_2023年6月.xlsx ・・・・ このようなファイル名が違うデータについて、毎月集計のためにコピーと貼り付けを繰り返しているようなケース。ファイル名を固定にする方法もあるが、データを識別するために固有なフ…
Power Query（パワークエリ）で、空白の列を追加したい時があります。 例えば、他のExcelの表に貼り付けするなどの都合で、列の順序や数合わせののために項目名はあるが、データは空白にしたいケースなど。 初歩的なことですが、Power Queryを初めて使用したとき、その方法が分からずに悩みました。 空白の列を追加する方法は、次の「新しい列を追加して同じ値を入力」の方法のようにカスタム列で、”null”を入れるが一番簡単な印象。 null は、何もないという意味。 shikumika.org 簡単な手順ですが、参考にカスタム列の画面イメージは次のとおり。 カスタム列の式で、小文字で"nu…
Excelで文字列から数字だけを抽出したい時があります。 例えば、名簿の名寄せをする場面などで、電話番号から余分な括弧やハイフンを除きたいなど。 このようなケースも、最近のExcelに標準で含まれているPower Queryは便利です。 以前は、以下の記事にあるようにユーザー定義関数を作成したりしていました。 shikumika.org これが、Power Queryだと標準の関数を使用して Text.Select([電話番号], {"0".."9"}) で数字だけを抽出できます。 手順 基本は、「例から列を追加」を活用することで作成可能です。 shikumika.org 具体的には、「例から…
PDFにある表（透明テキストつき）を編集できるようにエクセルに貼り付けする方法についてのメモ。 Excelの機能の1つであるPowerQueryでうまく取り込めました。 WordでPDFを開き、Excelに貼付する方法 adobeのページで変換 との違いについては、ページ下部に記載。 なお、PowerQuery は、最近のExcelに標準で含まれている機能。PowerQuery が含まれるバージョンの詳細はこちら。 PDF化された次のような表をExcelに取り込みたい 前提：PDF上でテキストは選択できる（透明テキストPDFの状態） PDFにある表 実施手順 ExcelのPowerQueryで…
PDFをExcelにインポート、繰り返し業務ならPower Query（パワークエリ）が便利
毎月、同じレイアウトのPDFファイルをExcelに転記するなど、「繰り返し、PDFからExcelへのデータ抽出業務」がある場合、Power Query（パワークエリ）が便利だったので備忘のメモ。具体的には、 行政が毎月更新、公開しているPDF形式（透明テキストPDFの状態）の一覧データがある。 分析のためにPDFファイルから必要箇所を毎月データ抽出し、Excelで編集する。という業務で、Power Queryを利用。
Power QueryでCOUNTIF関数のように各行の値の重複を数える方法（List編）
前回、Power QueryでCOUNTIF関数のように各行の値の重複を数える方法として、グループ化の機能を使用した方法を紹介しました。今回はListを使った方法です。 ただし、前提として、今回の方法は使用できる場面が少ない印象です。 大量データには不向きなので、その場合は前回の「グループ化」を使う方法が最適と思います。 shikumika.org 今回の方法だと数千件程度まででないと、処理時間がとても長くなる印象です。 （私の環境で、3000件のサンプルデータで試したところ、グループ化なら1秒程度の計算時間が、今回の方法で30秒ほどかかり、処理時間が大きく異なりました） アウトプットイメージ…
Power QueryでExcelのCOUNTIF関数のように、各行の値の重複を数える方法（グループ化編）
Power Query（パワークエリ）に、Excelの数式でいうCOUNTIF関数がありません。 名簿の名寄せなど、各行の値の重複を数えたいときに悩んだので解決方法のメモ。 良さそうな方法は次の2つ 「グループ化」を使う方法 List.Count関数、List.FindText関数を組み合わせて使う方法 ※ ただし、こちらは大量データには不向きな印象 今回は、「グループ化」を使う方法について説明します。 アウトプットイメージ 「名前」という列にある人と同じ名前の件数を「カウント」に表示するイメージ 作成手順 「テーブルまたは範囲から」で、クエリの作成 次のような、顧客ID、名前、都道府県、年代…
Power Query（パワークエリ）でフォルダ指定を相対パスにする方法
Power Query（パワークエリ）のデータソース場所の指定は、標準機能では絶対パスでの指定しかできないようです。 そうなると、データソースとなるファイルの場所を変更した場合、都度変更の手間が発生します。 社外など別環境の人に渡したり、毎月などデータソースの場所を都度変更したい場合など、どのようにデータソースを変更するかで悩みます。 特に、Power Queryに慣れていない人に渡すには、データソースの変更は難易度が若干高い。 その解決方法の一つとして、データソースを相対パスにする方法があります。 なお、相対パスでのフォルダ指定は、セキュリティ面でのリスクも検討必要らしいですが、使い方を限定…
