PowerQuery
ExcelのPowerQueryは、バージョンによって取込可能なデータソースが異なります。例えば、PDFをExcelにインポートする際にはPowerQueryが便利ですが、Office 2016やOffice 2019では利用できず、Microsoft 365のExcelで利用可能です。 JSONの取り込みは、Offic…
Power Query（パワークエリ）には、マトリクス表をリストに変換できる「列のピボット解除」という機能があります。 「列のピボット解除」には、「その他の列のピボット解除」と「選択した列のみをピボット解除」を含めて３種類あり、それぞれの処理の違いに…
前回はPower Query（パワークエリ）のカスタム関数の作成事例として、単票形式のExcelシートからセルの値を抽出する方法を紹介しました。今回は列のピボット解除などを活用して、同様にセルの値を抽出する方法の備忘録。 個人的な結論として、カスタム関数の…
Power Query（パワークエリ）のカスタム関数の作成事例として、単票形式のExcelシートからセルの値を抽出する方法の備忘録。 カスタム関数が利用できると、データ抽出等の作業効率化も可能。単票形式で紙印刷のレイアウトはPower Queryでの抽出も手間となる…
Power Queryで、元データの列名が変わるなど動的な列名に対応するため、列番号を示す数字で列を指定したい場合があります。Table.ColumnNames関数を利用すると、列名ではなく数値で列指定が可能で、方法の備忘録。 これにより、クエリ更新時に列名が変わる場…
Power Query（パワークエリ）の初心者の方が知っておくとよいエラーと対応の事例。簡単な事例ですが、通常の編集作業で起こりそうなエラーと対応方法の備忘録。Power Queryでのエラー対応の基本として紹介。 次の事例は、クエリの結果を変更したいケースで、…
Power Queryを初めて操作してから慣れるまでは、設定変更の影響でエラーが発生しないかが気になるところです。そこで、クエリ名の変更でテーブル名とシート名への影響を調べた備忘録です。 使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」。 Powe…
Power Query（パーワークエリ）は最近のExcelの標準機能として利用できます。Excelの機能ということで、使いながら覚えようとしたとき、最初に戸惑うことがいくつかあります。以下、簡単な事例で、初めてPower Queryを操作するときに知っておくと良いと思っ…
Power Query（パワークエリ）を活用する際、データソースをどこに配置し、どのように接続すると効率的かで悩みます。せっかく設定してもデータソースの保存場所や名前変更で、データソースエラーとなり、その都度の設定が必要になったり、部外や社外の人と結…
Excelのテーブルに「別ファイルのシートにあるデータ」をインポートし、テーブルのデータを更新するVBAサンプルの備忘録。 作成の背景は、Power Queryで同じファイル内のシート（テーブル）をデータソースとする管理をしたいが、元のデータ自体は別ファイル…
Power Query（パワークエリ）は、データの変換や統合に便利なツールです。しかし、データソースのパスが固定（絶対パス）の指定で、ファイルの場所が移動すると設定変更が手間です。データソースを相対パスにして動的に変更できると便利なので、最もシンプル…
リンクの設定等があるExcelファイルの場合、保存場所の変更に注意が必要です。特に、VBAやPower Queryなどで固定のファイルパスに依存した構築を行うと、Excelに不慣れな人がファイルの保存場所変更に対応できない等の問題が生じる可能性があります。 ファイ…
Excelのテーブル機能やPower Queryを含むファイルを他者に渡すことが妥当でないケースが時折あります。そこで、VBAで「 テーブルをセル範囲に変換してエクスポート」のサンプルを作成してみた備忘録です。
Excelの標準機能にあるPowerQuery（パワークエリ）を活用して、フォーマットは同じで、「ファイル名」が異なるデータのインポート業務を効率化する方法を調べた備忘録。 背景は、同一フォーマットで、ファイル名が取得時期や対象によって異なる次のようなフ…
Excelの標準機能にあるPowerQuery（パワークエリ）は、同一フォーマットで入力された複数ファイルを一つにまとめたい時に便利です。 毎月の売上データや、部門別の実績データといった複数のファイルを一つに結合する場面などに活用できます。これまでであれ…
PowerQuery（パワークエリ）は、毎月更新されるようなファイル（データソース）から、必要な情報の抽出を容易にできるツールで、とても便利です。 しかし、操作になれていないと、Excelであれば簡単なことの実現方法が分からずに困ることがあります。 例えば…
PowerQuery（パワークエリ）で、データソースのファイル名や、ファイルの場所が変更になると、次のようなデータソースのエラーが発生します。 DataSource.Error: ファイル ‘〇〇.xlsx' が見つかりませんでした。 例えば、データソースとなるファイル名に「売…
Power Queryを使用して「ExcelのCOUNTA関数」のように、各行ごとに空白ではない列の個数をカウントする方法の備忘録。 ExcelのCOUNTA関数は、特定の範囲内の空白でないセルの数を数えるために使用しますが、Power Queryで同様な関数はList.NonNullCount関数…
Power Queryを使用して「ExcelのCOUNTIF関数」のように、特定の列と同じ値の列の個数を各行ごとにカウントする方法の備忘録。 なお、特定の列ではなく、"指定した値"でカウントしたい場合は、前回の以下です。 shikumika.org アウトプットイメージ 横方向の…
Power Query（パワークエリ）を使用して「ExcelのCOUNTIF関数」のように、指定した値と同じ値の列の個数を各行ごとにカウントする方法の備忘録。 アウトプットイメージ 横方向のカウントで、「ExcelのCOUNTIF関数」でいうと次のような数式。縦方向のカウント…
Power Queryで、全角文字を半角文字に一括置換。Text.Replaceを使った時にエラーが発生して変換できなかったので原因と解決方法の備忘録。 全角文字を半角文字に一括置換が必要になった背景は、Excelで表記ゆれがある日付文字を日付データ（シリアル値）に変…
Excelで日付文字列に和暦の「元年」があるとき、DATEVALUE関数ではシリアル値には変換できない（反対に、西暦から和暦の「元年」への表示は可能だが）。 そのため「検索と置換」の機能で”元年”を”1年”に一括置換をしたり、SUBSTITUTE関数で置換したりで対応…
PowerQueryで数式を編集する際、日本語入力（漢字変換）をすると二重入力となり、都度、文字削除が必要でとても煩わしく感じていました。ATOK（ATOK for Windows ver32）を利用していましたが、Microsoft IMEに変更することで、適切に入力できるようになった…
VBAで、PowerQueryの更新を待ってピボットテーブルを更新するときの備忘録。PowerQueryの更新が完了してから次の処理を実行したいときはBackgroundQuery プロパティがFalseの状態にしておく。 以下は、PowerQueryの更新を待たずにピボットテーブルが更新され…
Excelで文字列から数字だけを抽出したい時があります。 例えば、名簿の名寄せをする場面などで、電話番号から余分な括弧やハイフンを除きたいなど。 このようなケースも、最近のExcelに標準で含まれているPower Queryは便利です。 以前は、以下の記事にある…
Power Query（パワークエリ）で、空白の列を追加したい時があります。 例えば、他のExcelの表に貼り付けするなどの都合で、列の順序や数合わせののために項目名はあるが、データは空白にしたいケースなど。 初歩的なことですが、Power Queryを初めて使用した…
Power Query（パワークエリ）を初めて利用するとき、色々な項目があって悩みます。しかし、列を追加する基本的な操作方法は、「列の追加」にある次の赤枠内を知っていれば十分に使える印象。 PowerQuery（パワークエリ）の基本的な操作方法 「列を追加」のポ…
Power Query（パワークエリ）の基本的な操作方法の備忘録。数式の書き方がわからない、数式のサンプルがみたいという場合は、「例からの列」が便利です。 求めるアウトプットのイメージをPower Queryに入力するだけで、必要な数式を自動作成してくれる便利な…
Power Query（パワークエリ）の基本的な操作方法の備忘録。 メニューにある「条件列」を使っての「条件に応じた値表示」では、andやorの条件指定ができないので、カスタム列を使った基本的な設定の手順です。andやorを使った複数条件で抽出もできます。 メニ…
Power Query（パワークエリ）の基本的な操作方法の備忘録。 新しい列を追加して同じ値が入力ができたら、次は条件に応じて値を変更したい時の手順です。 基本操作は、メニューにある「条件列」を使って実施します。 PowerQuery 手順 「列の追加」-「条件列」…