PowerQuery
ExcelでPowerQuery等を利用すると、セキュリティの警告で「外部データ接続が無効になっています」というメッセージが表示されることがあります。そのため、「コンテンツの有効化」が必要になります。 しかし、どの外部データに接続しているのかを確認しよう…
Power Query（パワークエリ）で、単票形式のExcelシートからセルの値を抽出する方法として、【Power Query】列のピボット解除で、単票形式のExcelシートからセルの値を抽出とは異なる方法として、Excelの数式でセルを参照し、値を抽出する方法の備忘録。 単…
最近のExcelに標準で含まれているPower Queryを使用し、Excelで、JSON形式のデータから値抽出する方法です。JSON形式の場合も、【Power Query】エクセルで複数のXML形式のデータをインポートして整理と同様に抽出可能です。以下、具体的な手順の事例です。 …
Excelで、複数のXML形式のデータをインポート（読み込み）して一つにまとめる方法です。セル内にあるXMLの場合、【Excel】FILTERXML関数でXPathを指定してXMLからデータ抽出の方法があります。 今回は、XML形式のファイルが複数ある場合に、最近のExcelに標…
PowerQueryで、ファイルを結合するクエリで自動作成される「ヘルパークエリ」は、慣れるまでその役割が不明な機能です。 ヘルパークエリを理解するポイントについて記事をまとめました。 内容： 各クエリの依存関係を理解 「パラメーター1」と「サンプル フ…
Power Queryは、クエリからカスタム関数を作成することができます。クエリから関数を作成すると、関数にクエリの編集を自動反映できるというメリットがあります。 どのような動作をするのか調べた備忘録です。 内容： 事例の前提 確認内容 「ファイルの変換…
前回、【Power Query】ヘルパークエリの「パラメーター1」と「サンプル ファイルの変換」を削除をしても、アウトプットであるクエリに影響がないことを記載しました。 今回は、「パラメーター1」と「サンプル ファイルの変換」の役割を調べた備忘録です。 内…
PowerQueryで、ファイルを結合するクエリで自動作成される「ヘルパークエリ」の動作を理解するために、「パラメーター1」と「サンプル ファイルの変換」を削除した場合の動作を調べた備忘録です。 内容： 事例の前提 「パラメーター1」と「サンプル ファイル…
Power Queryで、ファイルを結合する時に自動作成される「ヘルパークエリ」の動作を理解するために、ヘルパークエリをすべて削除して同じ結果を得るクエリに修正してみました。 内容： ヘルパークエリを削除する前提知識 事例の題材（ヘルパークエリ） クエリ…
Power Queryで、余分なクエリを削除しようとすると、「クエリ"〇〇"はもう一つのクエリで参照されているため削除できません:”△△”。」というエラーメッセージが出ることがあります。 これは、削除しようとしているクエリが他のクエリから参照されているために…
Power Queryで、複数のクエリが連携している場合、各クエリがどのクエリに依存しているかを視覚的に把握したい時があります。 そのための機能として、Power Queryには「クエリの依存関係」という機能があります。 ファイルを結合するクエリで作成された「ヘ…
PowerQueryエディターの [クエリ] ウィンドウでは、グループ機能を使用してクエリをカテゴリ別に整理することが可能です。 これは、クエリをフォルダのような構成で管理できますが、動作内容や影響が不明だったので調べた備忘録です。 内容： グループを使用…
PowerQueryの利用場面で、データソースを変更したいことは比較的多く、【Power Query】データソースの変更方法が基本的な操作です。 他にもデータソースを変更する方法はありますが、ファイルを結合するクエリでヘルパークエリが存在する場合、データソース…
Power Queryで、現在のExcelファイルのシートの内容を取得したい時があります。 しかし、テーブル等を介さず、直接取得する関数はありませんでした。 なお、使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2309）です。 Excel ブック…
Power Queryで、ブック内にある複数シートの表を一つのシートに結合する方法の備忘録。Power Queryで作成する「クエリ」と「結合したいシート」も同じブック内にしたい場合と、クエリ自体は別ブックでもよい場合で若干手順が異なる。 内容： アウトプットイ…
Power QueryのList.FirstNとTable.FirstN、LastNなどの関数は、リストやテーブルから最初（LastNの場合は末尾）のN個の要素を取得するために使用します。 これらの関数の第二引数countOrConditionは、数値または条件を指定できます。 数値を指定した場合は、…
Excelで重複しているデータを削除するとき、「データ」タブの重複削除とPower Queryでの重複削除では、残すデータの仕様に違いがあります。 具体的には、Excelの「データ」タブの重複削除は、リスト内で最初に出現する値が保持されるとなっています。 suppor…
PowerQueryで、ExcelのVLOOKUP関数の「検索の型をTRUE」（近似一致）にする方法の備忘録。Excelでは、「年齢」を「年代」に変換など、区間分け（ビニング）にVLOOKUP関数は便利です。 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に不慣れな人がファイルの保存場所変更に対応できない等の問題が生じる可能性があります。 ファイ…
