Excelでやってはいけないことの一つが「マトリクス表への直接入力」。これをやる人は必ずといっていいほど同形式の表をシートで分けて量産してしまう。見出しにセル結合を入れちゃう人もいる。そして後でデータをまとめたり集計したりする時に困る。ここまで…

Excelをメインアプリとして使っているのは企業規模が極端に小さい会社くらいで、中規模以上の会社では「他のアプリとの連携」で利用することが多いように思う。その中でもよく使われるのが「CSVファイルの読み込み」だろう。 この作業が複数あると大変。ファ…

日付形式が 8桁の数値（いわゆる「yyyymmdd形式」）になっていたり、時刻が「hhmmss」になっていたりするデータは時々ある。これを日付型や時刻型に変換してみよう。 「yyyyMMdd」形式の 8桁の数値を日付型に変換するのは簡単。「テーブル1」に「日付」列が…

時々あるんだけど「行全体から特定の値が１つでもあれば抽出」みたいな処理。 例えばこんな表で、日付列の中に１つでも「梨」がある行を抽出したい場合。抽出方法はいくつか考えらえる。 ひとつは、グループ化した列のリストを対象にして検索する方法。「テ…

クエリで２つのテーブルから一部のデータを抜き出す場合。 こういう時は、リストにしてから抽出するのがお勧め。抽出の処理が軽いし、他の列は後からマージして展開すればいいだけだから。 テーブルA・テーブルBに、それぞれに「名称」列がある場合、そこか…

クエリでグループ分けをする時に注意したいこと。 左の表から右の表に組み換えしたい場合、よくあるやり方は「グループ化→インデックス列追加→展開して列のピボット」という流れ。 パターンA let 分類テーブル = [ ブック = Excel.CurrentWorkbook(), ソース…

指定列でグループ分けして番号を振りたい時。 Excelの数式だったら「=COUNTIF(A$2:A2,A2)」を下にコピーする形でグループ連番列を作るだろうと思う。クエリでは「行のグループ化」と「インデックス列の追加」を使えば同じことができる。 事前に画像のような…

よみこんだクエリのテーブルに1行だけ空行を追加したいなと思うことはないだろうか。 必要かどうかは置いといて、やりたいと思った時にどうすればいいかは考えておきたい。 まず「０行のテーブル」を作るのはとても簡単。「テーブル1」の上位「0」行を抜き出…

指定された２つの日付の期間を計算したい時。DAX関数なら DATEDIFF関数の出番なんだけど、Power Queryでやるとどうなるのか。 DATEDIFFもどきを作る｜Power Query - Qiita ここの記事を見ていて自分もカスタム関数で DATEDIFを作ってみたくなった。どうせな…

タイトル通り。フォルダ指定で複数のブックを結合して読み込みたい時。 これが今回の完成目標。「フォルダから」を使って複数のファイルを縦に結合して読み込むのと同じ動作を、別の方法でやってみる。 「参照ファイル」フォルダの中はこんな感じ。 任意のフ…

プライバシーレベルの問題を無視するなら（下げないと使えないので）、クエリで自ブックを参照したい時や、自ブックと同階層のファイルを可変で参照したい時、セルに入力された値がパラメータとして使えると便利ではある。 というのも「当該ブック（保存済）…

列数が少なくて行数が多いデータの場合、そのまま印刷すると余白だらけでページ数がかさむので、適当な行数で横に表示したい場合の話。 INDEX/SEQUENCEだったり、MAKEARRAYだったり、古いバージョンでもINDEX/ROW/COLUMN/MOD/INT辺りを使えば数式でもできる…

例えば、住所の一覧から「北海道」「青森県」……みたいな都道府県名リストを基にして部分一致で抽出を掛けたい時。あるいは特定のキーワードを含むデータを抽出したい時。「部分位置で抽出」の用途は多岐にわたるかと思う。 社名リストの中から検索リストに該…

［データの取得］→［ファイルから］→［Excel ブックから］でファイルを指定した場合、ブックの中でシートが分かれていると、シートの数だけクエリを作らないとといけなくなってしまう。これをマージしようと思ったら更にクエリの追加も必要。というかシート…

梱包数に合わせてラベルを作る時に必要かもしれない処理。 例えば「55個」の商品を「10個単位」の梱包で分けると「5梱包と余り5個」なので、「10個入梱包×5」と「5個入梱包×1」のラベルが必要。これをクエリでやるとどうなるか。 ロット = 10, 個数リスト = …

データクレンジングが Power Queryの主な役割だと考えるなら、避けて通れないのがデータの組み換え。 他のアプリからデータを読み込んだら、こんな形だったってことが時々ある。 いわゆるｎ行一組のデータ（画像は6行）。これを項目別に組み換える場合、「数…

複数のテーブルをまとめる際、「クエリの追加（Table.Combine）」を使う前に必ずやっておかないといけないことがある。それは列名の統一。 例えば、下記のような列名が統一されていないテーブルが３つあるとする。 テーブル1 = #table( type table [名前=tex…

「年」を基にして、その年の「1月～12月」の月初日がテーブルに読み込まれるようにする場合。 年 = 2022, 年月リスト = List.Transform( {0..11}, each Date.AddMonths(#date(年, 1, 1), _) ), テーブル変換 = Table.FromColumns({年月リスト}, type table […