Power Queryで複数のファイルを集約するとき、ファイル名の文字列を検索してリストを集約する事例です。Text.Contains関数を活用し、Excelファイルの指定列の値がファイル名に含まれている行だけを抽出します。 内容： アウトプットイメージ 設定事例 （１）…

Power Queryで、Excelファイルを読み込み処理をするケースで、Excelの名前の定義に#REF!があるとエラー発生することがあったので備忘録です。 名前の定義に#REF!があれば必ずエラー発生ではありません。 エラーの内容 Excelファイルを読み込み処理をしようと…

Power Queryで、ユーザーフォームを準備してkintoneのデータをExcelに読み込む事例です。 ユーザーフォームはExcelのセルを使用した簡易的なものですが、利用者がPowerQueryエディターを起動せずに設定値を変更できるため、活用時の制約が少なくなります。 …

Power Queryでデータを抽出するとき、フィルター条件をセルで指定できると便利です。 Excelのセルであれば、PowerQueryのフィルター条件（指定値）をPower Queryエディターを起動せずに変更が可能です。また、数式を用いた動的な日付指定なども可能で自動化…

Power Queryで回収リストをもとに、データの抜け漏れをチェックするときの方法です。特定のリストに含まれていないデータを抽出する設定例です。 この事例では、47都道府県の集計結果が抽出できたかをチェックします。 この方法は、社員リストと出席リストを…

Power Queryの並び替えで、昇順・降順ではなく指定した任意の順番に出力したいときの方法です。都道府県の順に並び替えをする事例です。 内容： アウトプットイメージ 実施手順 1．並び替えの基準になるリストを準備する 2．元データと「並び替えの基準にな…

Power Queryで、Excelの表からではなく「空のクエリ」からテーブルを作成する方法です。数式がシンプルとなりやすいテーブル関数#tableを活用した事例です。 活用場面としては、PowerQueryの利用目的上、簡易なマスタテーブルを「Excelの表」とは別で持ちた…

最近のExcelに標準で含まれているPower Query（パワークエリ）は、マクロを利用せず、複数ファイルの結合やデータ抽出の自動化が可能なツールです。 ExcelユーザーがPower Queryを初めて操作するときに知っておくと良い用語について、個人的な備忘録です（正…

前回の【Power Query】kintoneからExcelに読み込む事例（ログイン名とパスワード情報の利用） をベースに、「ログイン名とパスワード情報」をパラメーターとして随時修正する事例です。パラメーターを利用して汎用性、メンテナンス性を高めています。 なお、…

Power Queryで、kintoneからExcelに読み込む事例です。kintoneのREST APIを使用しますが、ログイン名とパスワード情報で認証します。アプリ管理の権限がなく、APIトークンが不明な場合も連携可能です。 なお、この事例の注意点は次のとおりです。 ユーザー個…

Power Queryで、WebページからデータをExcelに読み込む事例です。kintoneのREST APIを使用し、PowerQueryでkintoneに登録されたレコードをExcelで読み込み、使い慣れたExcelで集計やグラフ化ができるようにします。 内容： アウトプットイメージ 前提条件 実…

Power Queryで「ログイン名:パスワード」をBase64エンコードの事例です。Power Query M 言語のBinary.FromText関数では実現できず、Binary.ToText関数とText.ToBinary関数を組み合わせる必要があります。 内容： アウトプットイメージ 事例の説明 数式内側の…

PowerQueryの「カスタム列の追加」でIF文を活用し、一つ上のレコードと違う値のときに印をつける事例です。指定した列で上のセルと同じ値なら空白で、違う値のときにアスタリスク（*）のマークをつけます。 内容： アウトプットイメージ 実施方法 基本の考え…

Power Queryで自動作成される式で、eachとアンダースコアについて調べた備忘録です。アンダースコアそのもの役割は、【Power Query】識別子 _ (アンダースコア) の理解にまとめたので、eachとの関係性についてです。 内容： Power Queryの「each」の基本 「e…

Power Queryで自動作成される式で、識別子 _ (アンダースコア、もしくはアンダーバーという)が使用されていることがあります。このアンダースコアは何を表すのか、役割を理解する備忘録です。 内容： 初心者がアンダースコアの中身を確認する方法 アンダース…

PowerQueryに少し慣れてくると、自動作成されたステップをまとめ、ステップを減らすことで、シンプルなステップにしたいと思うようになります。 特に、簡単な処理なのに自動作成されたステップが多くなると、他に簡略化できる方法がないのかと思ってしまいま…

Power Queryで、日付データから曜日の抽出する事例です。「例からの列」による方法、DateTime.ToText関数を使用する方法があります。 Excelの数式と同様、容易に抽出可能です。 内容： アウトプットイメージ 実施手順 日付データを「曜日名」に変換する方法 …

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

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

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

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

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

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

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」と「サンプル ファイルの変換」の役割を調べた備忘録です。 内…