Power Query
久々のパワークエリネタです。検索値が用意したリストとデータ一致するかをチェックする方法はいくつかあるんですけど、「～を含む」を検索する場合は方法が限られてきます。 上記の画像では、「電気・水道・ガス」のいずれかのワードを含む値をテーブル１か…
多分一番要望として多いからだと思うんですが、複数のファイルを一括で読み込むクエリの質問が増えてきています。 ただ、ヘルパークエリどころかパワークエリがどんなものかすら、よくわからないまま使っている人が多い印象です。 どうもネットを検索してみ…
今更なんですが、結構この説明をするのに時間を取られることがありまして…… 何もない状態から「空のクエリ」を作る方法を下記にまとめています。 エクセル上（ブック）からクエリを作成する方法 Power Queryエディタからクエリを作成する方法1 Power Queryエ…
２つのテーブルを縦に結合する時は、列の名称とデータ型が揃っている必要がありますよね。データ型については、結合した時に揃っていない列が any型になるだけですみますが、列名が異なると別々の列として結合されてしまいます。 「テーブル1」・「テーブル2…
2025年は、基礎的な操作についても取り上げていきたいと考えています。というわけで今回は、古いテーブルと新しいテーブルを突き合わせて、古いテーブルにはない追加された行だけを抜き出す方法について考えてみます。 いわゆる差分抽出（不一致クエリ）とい…
パワークエリでの文字列の変換には、「値の置換（Table.ReplaceValue）」を使いますよね。「A」を「B」に置き換えするような場面なら、これでできます。 ただパワークエリでは、今のところワイルドカードや正規表現が使えません。「A BCD」や「BCDA」のよう…
実のところ必要性はそれほど感じていないんですが、やれるかどうかに興味があったので「クエリ名の文字列からクエリを呼び出す方法」を考えてみました。 予め、上記の３つのテーブルは「テーブル1」「テーブル2」「テーブル3」として、クエリを作成済（接続…
慣れないと混乱すると思いますので、２つのリストを使って比較抽出する方法をまとめておきます。 「A」と「B」のリストがあるものとして、 ＡとＢのすべて = List.Distinct(A & B) もしくは = List.Union({A, B}) ＡとＢで重複するものだけ = List.Intersect…
会社名（法人組織名）から、法人格（「株式会社」など）とそれ以外を分ける方法について。 関数を使って数式でやるにしても、パワークエリでやるにしても、法人格のリストは用意したほうがいいです。 最新のエクセルなら、TEXTSPLITが使えるので数式のほうが…
今回は「複数のブックの複数のシートを結合して読み込む」クエリの作り方を説明します。ただし、このクエリはマウス操作だけではできません。Power Query エディタを開いて編集する必要があります。 「複数ブックを読み込んで縦に結合する」とか「複数シート…
以前に一回やったんですが、もう少し手軽にできないかとやってみました。複数の CSVファイルを、なるべく手間をかけずに結合して読み込んでみます。 指定フォルダに接続する CSVファイルを縦に結合する式を入力する おまけ：ファイル名列を追加したい場合 指…
金種計算（紙幣や硬貨などの通貨の枚数を計算する）については、昔から色んなやり方がありますし、条件を絞れば金種表は比較的簡単に計算できます。ただ、金額範囲と通貨範囲を指定してスピルで計算しようとすると、少し面倒になります。 金種自体は「金額を…
当番表やローテーションが被らないよう、ランダムに組み合わせたいと考えた時、従来であれば数式でやるのはそれなりに大変だったんですが、Microsoft365ならそうでもないなと思った次第です。 例えば、５×５の枠に１～５の５つの数字を、縦横（斜めは除外）…
購入履歴のデータを読み込むと、途中で間違いに気付いてキャンセルされた履歴も混じってきますよね。集計する際にはいらない情報なんで、今回はこれを取り除いてみようと思います。 今回の購入履歴データについては、下記を前提としています。 当日中のキャ…
パワークエリでファイルを読み込むと、ソースの下に「変更された型（Changed Type）」が自動的に作成されます。この機能、邪魔だなと思うことないでしょうか。 もちろん判定してくれることに否やはないんですが、必要ない時に勝手に追加されると、思いもよら…
基本的な機能なので、普通に使う分にはなんてことのない話なんですが。区切り文字を使って列を分割する際の話です。 機能としては至極単純で、区切りたい列を選択して「列の分割」を選ぶだけです。 区切りたい列を選択した状態で右クリック →［列の分割］→［…
何かしらの項目を基準にして集計する際、「A」「B」「C」……とある項目の内、「B」のデータが一つもないと、集計結果のテーブルは「A」「C」……と、歯抜け状態になってしまいます。そういう場合は、予め用意した「A」「B」「C」……テーブルに、集計結果を入れ込…
指定フォルダに入っている Excelファイルで、単純に「ブックの中のこのセルだけ」を読み込んで結合したい場合の手順をまとめてみました。 まずは参照用のフォルダに同形式のファイルを保存します。全部同じシート名・同じ配置が前提ですのでご注意を。 今回…
Power Queryで開始日から終了日までの連続している日付リストを作る場合、多分 List.Datesを使んじゃないでしょうか。 = List.Dates( 開始日, Duration.Days(終了日-開始日)+1, #duration(1, 0, 0, 0) ) learn.microsoft.com もちろんこれでいいんですけど、…
画像の通りなんですが、元のテーブルに値のない日付を追加して表示したい時は、どうすればいいでしょうか。 この場合、まずは「4/1」から「4/7」までが連番になっている日付テーブルを新規に作ったほうがいいです。元のテーブルは「テーブル1」として読み込…
表計算の世界で「やったら後で後悔することランキング」をとったら、上位３以内に入ってくるのは「セルに複数の情報を入力する」だと思います。因みに他の２つは「同じ形式の表をシート単位で量産する」と「セル結合で入力を省略する」です。異論は認めます…
クエリのマージを実行した後で、結合した列を展開（Table.ExpandTableColumn）した時に、行の順番がずれてしまったことはないでしょうか。 例えば下のようにテーブル1とテーブル2をマージして、「テーブル2」列から必要な列を展開すると、 「名前」列が元の…
個数の合わない配列（ジャグ配列）を、単純に１テーブルに変換しようとすると、エラーを起こしてしまいます。 文章で読んでもピンとこないかもしれませんが、例えば要素数が 10個の配列（例えば「{1..10}」）を 3分割した場合、最後の配列の要素数は 1つにな…
「カタカナをローマ字表記に変換する」記事は時々あります。数式でも WebAPIの力を借りれば簡単にできます。とはいえデータが個人情報の場合は、WebAPIで検索表示させるのは情報漏洩のリスクがあるので、避けたい向きもあるのではないでしょうか。というわけ…
簡単かと思ってやってみたら、意外と苦戦するかもしれません。「重複を省いて抽出」ではなく「重複している行を抽出」したい場合、どうすればいいでしょうか。 こういう時、エクセル感覚だと「それぞれの行に、一つ上までの行を参照する COUNTIF関数を仕込ん…
クエリのステップの中で、複数キーを指定して抽出したい時、数が多くなれば抽出用のリストを用意することになるでしょう。ただ「～と一致する」なら抽出条件に List.Containsを使えばいいんですが、「～を含む」を条件にしたい場合には、そうはいきません。 …
空いている行を上の値で埋めるのは「下方向にフィル」で簡単にできますが、その逆はどうでしょうか。もちろん「上方向にフィル」という意味ではなくてです。 つまり画像のように、先頭の値以外を表示しない設定です。「ピボットテーブルでやれ」、「クエリで…
複数のブックを統合するのに標準機能でやると、出現するのがヘルパークエリなんですが、これが苦手な人も多いのではないでしょうか。ただ、慣れれば他の用途にも応用できますので、是非とも仕組みを理解して活用して欲しいところです。 今回は「複数のシート…
ソースの読み込みを条件式で切り替えた場合、該当しないほうのデータは読み込まれるでしょうか。答えはノーです。つまり読み込む情報を小分けにしておいて、必要に応じて切り替えて使ったほうが処理は断然早くなります。 例えば A列が1行、B列が100万行のテ…
皆さんは、クエリでソースを読み込もうとしたらエラーが起きたことはないでしょうか。私はよくあります。 固定のパスでフォルダを参照していると、ふとした拍子にリンクが切れてしまうことがあるからです。 こんな時は慌てずに、右上の「設定の編集」ボタン…
