Excelワークシート操作に相当するPower Query操作のメモ

Excel関数に近い処理は、Power Queryではどれにあたるかな？という視点で、一度まとめてみました。
断りがない場合、Power Queryエディターで、テーブルでの話をしています。

※処理方法に抜け、漏れ等あると思います。ご意見、リクエスト等いただければ、なるべく対応します。

全般の注意事項

１．列の操作はやりやすく、行同士の操作はやりにくい。
　テーブル上のデータを、列名によって操作するのが基本のため、全く同じようにできるわけではないです。Accessと同様、別物と考えた方がよいです。
２．処理の順序で結果が変わる。
　例えば、ワークシート操作では、SUM関数をかけておけば、後から対象の値を編集しても、SUM関数の計算結果が変わりますが、Power Queryの場合は当初のSUM関数を掛けた時点の値で固定されます。
３．関数名を打つ場合は、ワークシート関数と異なり、大文字・小文字の区別が大事になる。
　Power BIのPower Queryエディタならインテリセンスが効きますが、Excelではまだ搭載されてません。
⇒ExcelもOffice365なら搭載されております。（Ver2.75.5649.201で確認済み）

四則演算等

・特定の列に一定の値を四則演算する

対象とする列を選んだ状態で、変換タブの「標準」メニューを使います。
※「指数」メニュー、「三角関数」メニューも同様。

・特定の列に別の列の値を四則演算する

列の追加タブ―「カスタム列」メニューから、計算式を入れます。「+-*/」の各記号はワークシート同様に使えます。

※上記の手順で、計算結果の列が追加されます。計算対象列が不要であれば、手動で削除します。

・四捨五入をしたい

SUM/AVERAGE/MAX/MIN（列・行）

・列（縦方向）の合計値を得たい

　対象となる1列を選択した状態で、変換タブの「統計」―「合計」をクリック。

・列（縦方向）の合計値を入れた列を追加したい

　1)数式バー左の「fx」マークをクリック
　2）自動で入るコードを、下記のように編集して、enter。

　完成図

・行の合計（合計列の追加）

対象となる複数列を選択した状態で、列の追加タブの「標準」―「加算」をクリック。

lookup系

・VLOOKUP関数みたいにしたい

クエリのマージで同様の効果が得られます。操作方法は下記のレファレンスを参照。
合成列などを作らなくても、複数条件一致で持ってこれるところは、VLOOKUPより便利です。
他方、読み取る列は列数では指定できず、列名で指定することになります。

クエリをマージする (Power Query)｜Microsoft

・HLOOKUP関数みたいにしたい

直接解決する関数はないので、マスタ側（データを取りに行く方）のクエリをVLOOKUPのときと同様の形に変換しておいて、クエリのマージをすることになります。
マスタ側の変換手順は下記のとおり。

変換前の状態

１）ホームタブ「1行目をヘッダーとして使用」―「ヘッダーを1行目として使用」

２）変換タブ―「入れ替え」

３）ホームタブ「1行目をヘッダーとして使用」―「1行目をヘッダーとして使用」
ちょうど、手順１と逆の操作になります。

変換後

日付・時刻操作

・任意の日付・時刻を入力したい

2019/1/1

#date(2019,1,1)

2019/1/1 13:00

#datetime(2019,1,1,13,0,0)

・曜日の列を追加したい

対象となる列を選んだ状態で、
列の追加タブ「日付」メニュー「日」―「曜日名」

・EOMONTH関数みたいにしたい

対象となる列を選んだ状態で、
列の追加タブ―「日付」メニュー「月」―「月の最終日」
※変換タブにも同様のメニューがある。

・EDATE関数をやりたい

こちらは手間がかかります。列の追加タブ―「カスタム列」メニューをクリック後、下記のような具合でコードを入れます。下記の例では、2ヵ月後を求めています。

文字列の処理

・先頭（最後）から指定数だけ文字を取りたい

変換タブ―「抽出」―「最後の文字」をクリック。
※列の追加でも同様の操作ができます。

・文字の置換をしたい

変換タブ―「値の置換」―「値の置換」をクリック。下記は改行を入れた様子。

・数値を指定の桁数に揃えた文字列にしたい

列の追加タブ―「カスタム列」をクリックし、下記のようなコードを入力します。
下記は「Column1」という列を4桁表示にするコードです。

Number.ToText([Column1],"0000")

SUMIF/SUMIFS関数など条件付集計をしたい

　特別の関数はありません。自分で好きにフィルタしてから、先の合計処理をすればよいだけです。MAX/MIN等も同様。

全体の評点平均を上回る件数を求めた様子①

全体の評点平均を上回る件数を求めた様子②

一つ上の行の値を使った処理をしたい

　できなくはないけど、向いてはいません。なるべく、そのような処理にならないようにデータを用意するのがよいです。
下記は、上の行の値との差額を求めた例です。

※累積計算をしたいような場合は別の方法を取ったほうがよく、本格的にコードを書いたほうがいいと思います。累積計算については下記の @PowerBIxyz さんの記事が詳しいです。
Power Query の List.Generate 関数ってなんだよー - Qiita

テストした環境

Excel（Office365：バージョン1904 32ビット）
Power Queryバージョン：2.68.5432.241 32 ビット