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 ビット