Power Query
画像の通りなんだけども、元のテーブルに値のない日付を追加して表示したい場合どうするか。 この場合、まずは「4/1」から「4/7」までが連番になっている日付テーブルを新規に作ったほうがいい。元のテーブルは「テーブル1」として読み込み済みだとして、 日…
表計算の世界で「やったら後で後悔することランキング」をとったら、絶対上位３以内に入ってくるのが「セルに複数の情報を入力する」だと思う。因みに後の２つは「同じ形式の表をシート単位で量産する」と「セル結合で入力を省略する」で間違いない。 都道府…
クエリのマージを実行した後で、結合した列を展開（Table.ExpandTableColumn）した時に、行の順番がずれてしまったことはないだろうか。 例えば下のようにテーブル1とテーブル2をマージして「テーブル2」列から必要な列を展開すると、 「名前」列が元の順番…
個数の合わない配列（ジャグ配列）を、単純に１テーブルに変換しようとすると、エラーを起こしてしまう。例えば要素数が 10個の配列（例えば「{1..10}」）を 3分割した場合、最後の配列の要素数は 1つになる。これを Table.FromRowsでテーブルに変換しようと…
「カタカナをローマ字表記に変換する」ネタは VBAでよく見かける。数式でも WebAPIの力を借りれば超絶簡単にできる。これをクエリでやってみよう。 単純には置換リストを作って置き換えればいいので、例外処理を除けばそんなに難しい話ではない。ただし、何…
簡単かと思ってやってみたら、意外と苦戦するかもしれない。「重複を省いて抽出」ではなく「重複している行を抽出」したい場合、どうすればいいか。 こういう時、エクセル感覚だと「それぞれの行に、一つ上までの行を参照する COUNTIF関数を仕込んで……」みた…
クエリの中で複数のキーを指定して抽出したい時、抽出用リストがある場合はそれを利用するにこしたことはない。 例えば「リストの値と等しい行を抽出したい」場合は、Table.SelectRowsの条件を下記のように記述すればいい。 each List.Contains( {"中央区", …
空いている行を上の値で埋めるのは「下方向にフィル」で簡単にできるけど、その逆はどうだろうか。もちろん「上方向にフィル」という意味ではなくて。 つまり先頭の値以外を表示しない。「ピボットテーブルでやれ」とか「クエリでやることか」とかいわれると…
複数のブックを統合するのに標準機能でやると、出現するのがヘルパークエリなんだけども、これが苦手な人も多いのではないかと思う。でも慣れれば超便利なので、是非とも仕組みを理解して活用して欲しい。もちろん他の用途にも応用できる。 今回は「複数のシ…
ソースの読み込みを条件式で切り替えた場合、該当しないほうのデータは読み込まれるか。答えはノー。つまり読み込む情報を小分けにしておいて、必要に応じて切り替えて使ったほうが処理は断然早くなる。 例えば A列が1行、B列が100万行のテーブルがあるとす…
皆さんは、クエリでソースを読み込もうとしたらエラーが起きたことはないだろうか。私はよくある。 固定のパスでフォルダを参照していると、ふとした拍子にリンクが切れてしまうことがあるからだ。 こんな時は、慌てずに右上の「設定の編集」ボタン、もしく…
Power Queryは、ファイル内のデータを読み込む以外にも色んな使い道がある。例えばフォルダコネクタも便利な機能のひとつだ。フォルダのパスを指定して、サブフォルダを含めたファイルのリストを作成したい場合に使える。 例えば、Cドライブ直下に「親フォル…
画像で見た方が分かりやすいと思うので、早速だが下のような表があるものとする。 「日」の列と「時刻」の行で構成されたマトリクス表から「日時」と「名前」の表に組み替えしたい場合。何となく「列のピボット解除」をすれば良さそうなのは分かると思う。 …
以前とある業務で、引き継ぎ相手に Web送信用のCSVファイルを作らせて、自作ファイルと比較しようと思った時のこと。チェックしようとして気付いたのだが、何故か引き継ぎ相手が作ったファイルは私のものとは行の並びが違っていた。送信するデータの順番自体…
ピボット解除は便利なんだけど、それはあくまで見出しが１行の場合に限られる。元の表が一覧表の場合、得てして見出しが複数行にわたる場合もあるだろう。そういう場合、どうすればまともな形に組み替えできるか。 ２行とは限らず、３～４行に分かれている場…
表計算の世界で、同じ形式の表をシート単位で分けてしまうのは悪手以外の何ものでもない。ところが、Excelに不慣れな人は必ずといっていいほどこれをやってしまう。 例えば、A列に「名前」、B列に「値」が入力されている表が「Sheet1」から「Sheet10」まで並…
前回の逆。横並びのデータを縦に組み替える。 とはいえ、これクエリだと超簡単。何故なら「ピボット解除」があるから。 まずはテーブルを読み込む。 作った表にテーブル書式を設定（テーブル名「テーブル1」）。 「テーブル1」を選択した状態で ［データ］→…
縦並びのデータを区分を指定して横方向に組み替えしたいという質問は、度々目にするんだけど、グループ化したテーブルに連番列を追加する操作が必要なので、マウスを使った操作だけでは難しい。マウス操作中心でやる方法は、以前に書いたので割愛。 というこ…
検索用の条件テーブルを使って一致するデータを抜き出す場合、単純にはテーブル同士をマージしてやればいい……んだけども、マージが絶対に必要という訳ではない。 というわけで、まずはマージを使って「表1」から「表2」と複数条件で一致するデータを抜き出し…
読み込んだデータが 24時間以上を含む時刻データ（表示形でいうと「[h]:mm」）だった場合、クエリで読み込むと number型になってしまう。 ただシリアル値のままだと使いづらいので、期間型（duration型）に変換してしまおう。 期間型に変換 = Table.Transfor…
クエリを読み込んだ出力結果のテーブルの行数を固定にしたいと思う人が少なからずいるらしい。おそらく出力用の書式に直接クエリからデータを書き出しているのだろうと思う。本来の使い方としてはおかしいんだろうけど、ここは「なんでもエクセル」なので考…
１つのブックに複数のテーブルがあって、指定したテーブルの情報をどのテーブルから読み込んだか分かるように縦結合でまとめたい場合。 画像の「本支店名」列のことを考えないなら、結合したいテーブルを全部読み込んで、「Table.Combine」か「&」で繋ぐだけ…
前回の「住所から郵便番号を検索する」を更に応用してみる。郵便番号を検索するついでに、住所を「都道府県」「市区町村」「町域（地名）」で区切ってしまおう。 やることはあんまり変わらないので、説明はなし。今回も、ExcelAPIを使わせてもらう。 let url…
前回の反対パターン。住所から郵便番号を検索する方法。 今時（Microsoft365）は数式でもできなくはないんだけど…… =TEXT(WEBSERVICE("https://api.excelapi.org/post/zipcode?address="&ENCODEURL([@住所])),"000-0000") とりあえず今回は PowerQueryでやっ…
数式でやるなら郵便番号データをダウンロードしてきて検索すればいいんだけど、毎回それをやるのは面倒だし、かといって WebAPIを使う方法だといちいち再計算がかかるので都合が悪い。数十件くらいなら、Power Queryでやってしまえばいいかなと思う。 今回は…
という訳で前回の数式版に続いてクエリ版。問題をランダムに抽出するだけでなく、正解以外の候補を含めてランダムに出題する。 これが完成形。マクロでやるなら制限時間を付けてボタン選択したら履歴を残して新しい問題を表示させて……みたいなこともできるけ…
前回とやることはほぼ同じ。今回はひらがなをカタカナに変換してみる。 今回も置換リストをまず用意。面倒なので先に作ったものをアップしておく。 置換リストlet BinaryText = "RdCxUUNBEMDQXhw7MNh3u78WIPDe3u+A/mEYC2Uahe/j4/L5fb893i7Xv3i+Xb6ur/fOe/fde…
Excelだったら JIS関数・ASC関数で簡単に済む話なんだけど、Power Queryには全角半角変換の機能や関数が現時点では用意されていない。テーブルに読み込んでからワークシート関数で変換してやってもいいっちゃいいけど、それではどうにも不格好である。 クエ…
本来、同形式の表はシート単位で分けてはいけないんだけども、印刷物感覚で表を作っている人は必ずといっていいほど、これをやってしまう。ひどい場合、何十枚どころか１００枚を超えてシートを増やしてしまう人もいる。こうなってしまったら手動でまとめる…
Power Queryで Excelの TODAY関数にあたるのは「DateTime.LocalNow」なのだが、抽出する時には別の関数を使うことが多い。「当日（に関連する情報）と一致するか」を返す関数が存在するからだ。 例えば本日のデータだけが欲しい時は、フィルタの抽出条件で「…
