見出し画像

Power Query M 式言語 カスタム関数作成1(別行の情報を参照 他)

Power Queryの式メモです。
別の行の情報を参照した新しい列を作りたいことがよくあるので練習がてら作成しました。
練習用にとりあえずアルファベットが並んだテーブルを作る式↓

let
   /* A~Zまでの大文字アルファベットが並んだテーブルを作る
      列名は'ALPHABET'
    */
   Alphabets = Table.FromList(
       {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"},
       null,
       { "ALPHABET" }
       )
in
   Alphabets

それぞれコメント内容どおりの列を追加するカスタム関数たち↓
※1行目の**…**内の文字列を名称に指定しています。

let
   /* **AddLowerColumn**
      指定したテーブルに、colNameで指定した名前の列の情報を小文字化した
      newColNameで指定した名前の列を追加するカスタム関数
    */
   Result = (tbl as table, newColName as text, colName as text) =>
   Table.AddColumn(tbl, newColName, each Text.Lower(Record.Field(_, colName)))
in
   Result
let
   /* **AddIndexColumn**
      指定したテーブルの、行番号を表示する
      newColNameで指定した名前の列を追加する
    */
   Result = (tbl as table, newColName as text) =>
   Table.AddIndexColumn(tbl, newColName, 1, 1)
in
   Result
let
   /* **AddNextRowColumn**
      指定したテーブルの、指定列の次行の情報を参照する
      ※すでにindexという名前の列があるとエラー発生する
    */
   Result = (tbl as table, newColName as text, colName as text) =>
   let
       WithIndexColumn = Table.AddIndexColumn(tbl, "index", 1, 1), // 別行の情報を参照する準備
       Added = Table.AddColumn(WithIndexColumn, newColName, each Table.Column(WithIndexColumn, colName){[index]}),
       Removed = Table.RemoveColumns(Added, "index") // 不要なインデックス列は消す
   in
       Removed
in
   Result
let
   /* **AddOtherRowColumn**
      指定したテーブルの、指定列の、指定オフセット分移動した行の情報を参照する
      -1で前行、0で同行、1で次行
      ※すでにindexという名前の列があるとエラー発生する
    */
   Result = (tbl as table, newColName as text, colName as text, offset as number) =>
   let
       WithIndexColumn = Table.AddIndexColumn(tbl, "index", offset, 1), // 別行の情報を参照する準備
       Added = Table.AddColumn(WithIndexColumn, newColName, each Table.Column(WithIndexColumn, colName){[index]}),
       Removed = Table.RemoveColumns(Added, "index") // 不要なインデックス列は消す
   in
       Removed
in
   Result

アルファベットのテーブルに対して、作ったやつを全部適用する式

let
   res1 = AddLowerColumn(Alphabets, "alphabet", "ALPHABET"),
   res2 = AddIndexColumn(res1, "indexColumn"),
   res3 = AddNextRowColumn(res2, "nextAlphabet", "ALPHABET"),
   res4 = AddOtherRowColumn(res3, "previousAlphabet", "ALPHABET", -1)
in
   res4

全部適用した結果イメージ

画像1

作業用の列を作らない方法はあるのかなあ…
あとはList.Generate使えば色々できそうだけど、いまいち理解できていないです。

この記事が気に入ったらサポートをしてみませんか?