shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

PowerQuery

【Power Query】ゼロ埋めでText.PadStartの活用例とエラー対応例

PowerQueryのゼロ埋めでText.PadStartの活用とエラー対応例です。前回の【Excel】数値の先頭に0を追加して桁数を揃えた文字列に変換する関数と同様の処理をPowerQueryで実施します。 内容: アウトプットイメージ 基本知識 具体的手順 1.設定前の状態 2.…

【Power Query】グループごとのTableから値取得する事例

Power QueryでグループごとのTableから値取得する事例です。 この方法は、複数の「単票形式のExcelシート」を処理したい場合や、複数行ごとの表を一行に変換したい場合などに活用できます。 内容: アウトプットイメージ 事例の前提 具体的な手順 1.Table…

【Power Query】複数行ごと(例外あり)の表を一行に変換する方法 

Power Queryで複数行ごとの表を1行に変換する事例として、繰り返しの行数が変動する場合の対応事例です。 繰り返しの行数が固定の場合は、【Power Query】複数行ごとの表を1行に変換(一つ下の値を参照)をご覧ください。 内容: アウトプットイメージ Pow…

【Power Query】空白セルがある場合に「上のセルと同じ値」を一括入力

Excelの作業時、空白セルがある場合に上のセルと同じ値を一括入力して集計等をしたいことがあります。そのような時、最近のExcelの標準機能として利用できる「PowerQueryのフィル機能」が便利です。 この機能を使うと、特定の列の空白セルを、空白でない最後…

【Power Query】複数行ごとの表を1行に変換(一つ下の値を参照)

Power Queryで複数行ごとの表を1行に変換する事例として、一つ下の行の値を参照する方法です。他にも変換する方法はありますが、Excelのセル参照による変換イメージに近い方法です。 内容: アウトプットイメージ 事例の前提 手順 1.「カスタム列」の追加…

【Power Query】Excelのように一つ上の行の値を参照する方法

Power QueryでもExcelのように一つ上の行の値を参照することは可能です。Power Queryは列(フィールド)を基本に処理しますが、「一つ上の行の値を参照する方法」を理解しておくと作業の効率化が図れます。 内容: アウトプットイメージ 事例の前提 Excel ブ…

【Power Query】外部データ接続をエディターを起動せずに確認する方法

ExcelでPowerQuery等を利用すると、セキュリティの警告で「外部データ接続が無効になっています」というメッセージが表示されることがあります。そのため、「コンテンツの有効化」が必要になります。 しかし、どの外部データに接続しているのかを確認しよう…

【Power Query】単票形式のExcelシートから数式も組み合わせてセルの値を抽出

Power Query(パワークエリ)で、単票形式のExcelシートからセルの値を抽出する方法として、【Power Query】列のピボット解除で、単票形式のExcelシートからセルの値を抽出とは異なる方法として、Excelの数式でセルを参照し、値を抽出する方法の備忘録。 単…

【Power Query】エクセルで複数のJSON形式のデータをインポートして整理

最近のExcelに標準で含まれているPower Queryを使用し、Excelで、JSON形式のデータから値抽出する方法です。JSON形式の場合も、【Power Query】エクセルで複数のXML形式のデータをインポートして整理と同様に抽出可能です。以下、具体的な手順の事例です。 …

【Power Query】エクセルで複数のXML形式のデータをインポートして整理

Excelで、複数のXML形式のデータをインポート(読み込み)して一つにまとめる方法です。セル内にあるXMLの場合、【Excel】FILTERXML関数でXPathを指定してXMLからデータ抽出の方法があります。 今回は、XML形式のファイルが複数ある場合に、最近のExcelに標…

【Power Query】ヘルパークエリを理解するポイント

PowerQueryで、ファイルを結合するクエリで自動作成される「ヘルパークエリ」は、慣れるまでその役割が不明な機能です。 ヘルパークエリを理解するポイントについて記事をまとめました。 内容: 各クエリの依存関係を理解 「パラメーター1」と「サンプル フ…

【Power Query】クエリから関数を作成すると、関数にクエリの編集を自動反映できる

Power Queryは、クエリからカスタム関数を作成することができます。クエリから関数を作成すると、関数にクエリの編集を自動反映できるというメリットがあります。 どのような動作をするのか調べた備忘録です。 内容: 事例の前提 確認内容 「ファイルの変換…

【Power Query】ヘルパークエリの「ファイルの変換」は「サンプル ファイルの変換」と連動

前回、【Power Query】ヘルパークエリの「パラメーター1」と「サンプル ファイルの変換」を削除をしても、アウトプットであるクエリに影響がないことを記載しました。 今回は、「パラメーター1」と「サンプル ファイルの変換」の役割を調べた備忘録です。 内…

【Power Query】ヘルパークエリの「パラメーター1」と「サンプル ファイルの変換」を削除

PowerQueryで、ファイルを結合するクエリで自動作成される「ヘルパークエリ」の動作を理解するために、「パラメーター1」と「サンプル ファイルの変換」を削除した場合の動作を調べた備忘録です。 内容: 事例の前提 「パラメーター1」と「サンプル ファイル…

【Power Query】ヘルパークエリを削除して同じ結果を得るクエリに修正

Power Queryで、ファイルを結合する時に自動作成される「ヘルパークエリ」の動作を理解するために、ヘルパークエリをすべて削除して同じ結果を得るクエリに修正してみました。 内容: ヘルパークエリを削除する前提知識 事例の題材(ヘルパークエリ) クエリ…

【Power Query】もう一つのクエリで参照されているため削除できませんの解決

Power Queryで、余分なクエリを削除しようとすると、「クエリ"〇〇"はもう一つのクエリで参照されているため削除できません:”△△”。」というエラーメッセージが出ることがあります。 これは、削除しようとしているクエリが他のクエリから参照されているために…

【Power Query】クエリの依存関係を把握(ヘルパークエリでの事例)

Power Queryで、複数のクエリが連携している場合、各クエリがどのクエリに依存しているかを視覚的に把握したい時があります。 そのための機能として、Power Queryには「クエリの依存関係」という機能があります。 ファイルを結合するクエリで作成された「ヘ…

【Power Query】エディターの [クエリ] ウィンドウでカテゴリに整理

PowerQueryエディターの [クエリ] ウィンドウでは、グループ機能を使用してクエリをカテゴリ別に整理することが可能です。 これは、クエリをフォルダのような構成で管理できますが、動作内容や影響が不明だったので調べた備忘録です。 内容: グループを使用…

【Power Query】ヘルパークエリがある場合のデータソース変更方法

PowerQueryの利用場面で、データソースを変更したいことは比較的多く、【Power Query】データソースの変更方法が基本的な操作です。 他にもデータソースを変更する方法はありますが、ファイルを結合するクエリでヘルパークエリが存在する場合、データソース…

【Power Query】現在のExcelファイルのシートを直接取得する関数はない

Power Queryで、現在のExcelファイルのシートの内容を取得したい時があります。 しかし、テーブル等を介さず、直接取得する関数はありませんでした。 なお、使用のExcelは「Microsoft® Excel® for Microsoft 365 MSO」(バージョン2309)です。 Excel ブック…

【Power Query】ブック内にある複数シートの表を一つのシートに結合する方法

Power Queryで、ブック内にある複数シートの表を一つのシートに結合する方法の備忘録。Power Queryで作成する「クエリ」と「結合したいシート」も同じブック内にしたい場合と、クエリ自体は別ブックでもよい場合で若干手順が異なる。 内容: アウトプットイ…

【Power Query】List.FirstNやTable.FirstNのcountOrConditionの理解

Power QueryのList.FirstNとTable.FirstN、LastNなどの関数は、リストやテーブルから最初(LastNの場合は末尾)のN個の要素を取得するために使用します。 これらの関数の第二引数countOrConditionは、数値または条件を指定できます。 数値を指定した場合は、…

【Excel】「データ」タブの重複削除とPowerQueryで重複削除の違い

Excelで重複しているデータを削除するとき、「データ」タブの重複削除とPower Queryでの重複削除では、残すデータの仕様に違いがあります。 具体的には、Excelの「データ」タブの重複削除は、リスト内で最初に出現する値が保持されるとなっています。 suppor…

【Power Query】VLOOKUPの近似一致と同様処理:区間分け(ビニング)の方法

PowerQueryで、ExcelのVLOOKUP関数の「検索の型をTRUE」(近似一致)にする方法の備忘録。Excelでは、「年齢」を「年代」に変換など、区間分け(ビニング)にVLOOKUP関数は便利です。 PowerQueryでも同様の処理を実現したい場合があり、その手順は次のとおり…

【Power Query】Excelバージョンによる取込可能なデータソースの違い

ExcelのPowerQueryは、バージョンによって取込可能なデータソースが異なります。例えば、PDFをExcelにインポートする際にはPowerQueryが便利ですが、Office 2016やOffice 2019では利用できず、Microsoft 365のExcelで利用可能です。 JSONの取り込みは、Offic…

【Power Query】列のピボット解除で、マトリクス表をリストに変換

Power Query(パワークエリ)には、マトリクス表をリストに変換できる「列のピボット解除」という機能があります。 「列のピボット解除」には、「その他の列のピボット解除」と「選択した列のみをピボット解除」を含めて3種類あり、それぞれの処理の違いに…

【Power Query】列のピボット解除で、単票形式のExcelシートからセルの値を抽出

前回はPower Query(パワークエリ)のカスタム関数の作成事例として、単票形式のExcelシートからセルの値を抽出する方法を紹介しました。今回は列のピボット解除などを活用して、同様にセルの値を抽出する方法の備忘録。 個人的な結論として、カスタム関数の…

【Power Query】カスタム関数の作成事例:単票形式のExcelシートからセルの値を抽出

Power Query(パワークエリ)のカスタム関数の作成事例として、単票形式のExcelシートからセルの値を抽出する方法の備忘録。 カスタム関数が利用できると、データ抽出等の作業効率化も可能です。 単票形式で紙印刷のレイアウトはPower Queryでの抽出も手間と…

【Power Query】動的な列名に対応するため、列を数値で指定する方法

Power Queryで、元データの列名が変わるなど動的な列名に対応するため、列番号を示す数字で列を指定したい場合があります。Table.ColumnNames関数を利用すると、列名ではなく数値で列指定が可能で、方法の備忘録。 これにより、クエリ更新時に列名が変わる場…

【Power Queryの初歩】簡単な事例説明(起こりそうなエラーと対応の事例)

Power Query(パワークエリ)の初心者の方が知っておくとよいエラーと対応の事例。簡単な事例ですが、通常の編集作業で起こりそうなエラーと対応方法の備忘録。Power Queryでのエラー対応の基本として紹介。 次の事例は、クエリの結果を変更したいケースで、…