Power Query のM言語で、一つ上のレコードを参照する - Window関数的なふるまい

# 背景

入力用のExcel君を、Power BIに取り込む際に、年と月が縦2レコードで表現されている事はよく目にします。

例えば、こんなシートです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/9a4199fe-d410-6410-2c02-122489eef75f.png)


データセットとして、各種DBやサービスに取り込む際には、**レコード単位にしたい**のですが。例えば、以下の様にですね。

- 時間
- 種別
- 数量


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/2b1bfd77-e084-d438-244e-9fa8607ddfa8.png)


この例の場合は、`時間`に相当するデータが、2行にまたがっています。

年 - 3レコード目
月 - 4レコード目

これ、一つにするのは結構大変なんです! Excelの様な表計算ソフトであれば、シート内の任意の行を参照できますが。BIツールやデータベースの場合は、任意の列の参照は大問題😭

ここでは、レコードが別になっている`年`と`月`を、一つのレコードにマージする手順をやっていきます。

# 作戦

大まかな作戦を。他にもっといい作戦もあるかもしれませんが...

- Pivot で**行列変換**をする
    - このデータは数量データが横に並んでいますからね
- そのために、年月の列をマージする必要あり
- マージする処理を記述すると、数量のデータにも影響がでる。そのために**クエリーを2分割**して後でマージ
- 後でマージするために最終系のデータセットの**列の項目**は決めておく。各クエリーで同じにしておく
- 今回は
    - 年月
    - 電力会社
    - 時間区分
    - 単位
    - 数値

# 手順

## 1. ExcelのデータをPower BIに取り込む

これは、諸々既存の手順をご参照ください。

取り込み時に、いきなり読み込むのではなく、[データの変換] を選択して、Power Query エディター を起動させます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/a31df0a0-48b5-8f26-f058-fb38c868f3ad.png)


## 2. 列名の値が入っているレコードを最上位に

毎回ですけどね。Excelでの入力データの場合は、人へ伝えるための表自体の説明などが入っています。
このケースの場合は、データの説明のための

- 年が2行目
- 月が3行目

に入っています。最初のExcelとの違いがあります。Power Queryが、**列名をヘッダーにする処理**を行ってくれているので、1行減っています。それより上の1行目は不要です。

- [行の削除] - [上位の行の削除]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/d3ab1ab3-b3ae-bbbc-68b5-98245d8f8455.png)

- 上位の行の削除 画面にて「1」を入力して、上位1行を削除します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/885456e7-5b31-2313-16be-260a0e008cc1.png)

## 3. データ型を整理

この後、列を結合したりします。

各列のデータ型ですが、Power Queryが数字か文字か迷っている表記、つまり、`任意`になっているものが出てくることが多いです。今回は、「年月」の時間の値を作成するため、**文字列結合**を途中で行います。そのため、いったん全ての数値の入っている列を**テキスト型に変換**します。

- 全ての列を選択します

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/4ae03630-91fa-328a-bf31-1cac0eb5071e.png)

- メニューの[変換]の中に[データ型]という項目があります。ここから[テキスト]を選択して、全ての列のデータ型をテキスト型にします

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/f15e449c-eaa8-7ac3-96cc-e20114014beb.png)

## 4. 上の行を参照する処理 [このBlogの本題😊]

ここから、年月を作っていきます。

Window関数があれば、一発で出来そうです。ですが、Power Queryの中で書いている言語は **M 言語** で。そこには、Window関数は見当たりません。

M 言語:
https://learn.microsoft.com/ja-jp/powerquery-m/

Table関数やRecord関数はありますが。自分のいるレコードから相対参照をするような方策が、直ぐには思いつきませんでした。

:::note info
実は自分で何とかしようと1時間程度、試行錯誤してみました😅

これはこれで、ネットで検索するとDAX関数とM言語がごちゃごちゃの記事が合ったりという事がわかったり。M言語のドキュメントに慣れる、良い機会でした。

回答だけを求めてしまうと、概念やアーキテクチャ、そして**雰囲気**が掴めないことが多いです。
日頃慣れてない技術・ツールを使う際には、この1-2時間を大事にしたいですね。
:::


さて、**ドラフト**は当然 Microsoft Copilot (Bing Chat) に作成してもらいました😎

Microsoft Copilot:
https://copilot.microsoft.com/

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/8478679a-420d-5c8f-866c-b434719c024f.png)


Prompt:

```shell
Power QueryのM言語を使って、今いる行の一つ上の行のデータを参照する方法を教えてください。
```

出力文字列:

```shell
Power QueryのM言語で現在の行の一つ上の行のデータを参照するには、以下の手順を実行します。

1. **インデックス列の追加**:
   - Power Queryエディターで、`追加列`タブを選択し、`インデックス列`を追加します。これにより、各行に一意のインデックスが割り当てられます。

2. **カスタム列の追加**:
   - 次に、`カスタム列`を追加します。カスタム列の数式に以下のように入力します：
     ```m
     = try Table.AddIndexColumn(#"PreviousStepName", "Index", 0, 1, Int64.Type) otherwise null
     ```
     ここで、`PreviousStepName`は前のステップの名前に置き換えてください。

3. **一つ上の行の値を参照**:
   - カスタム列の数式で、インデックスを使用して一つ上の行の値を参照します。例えば、前の行の値を取得するには以下のようにします：
     ```m
     = try #"PreviousStepName"{[Index]-1}[ColumnName] otherwise null
     ```
     ここで、`ColumnName`は参照したい列の名前に置き換えてください。

4. **不要なインデックス列の削除**:
   - 最後に、追加したインデックス列が不要であれば削除します。

この手順により、現在の行の一つ上の行のデータを参照することができます¹²。

何か他にお手伝いできることがあれば教えてください！

ソース: Copilot との会話、 2024/7/30
(1) 【Power Query】Excelのように一つ上の行の値を参照する方法 .... https://shikumika.org/entry/powerquery-excel-cell.
(2) Power Query M 数式言語のリファレンス - PowerQuery M .... https://learn.microsoft.com/ja-jp/powerquery-m/.
(3) Power Query M 関数参照 - PowerQuery M | Microsoft Learn. https://learn.microsoft.com/ja-jp/powerquery-m/power-query-m-function-reference.
(4) Power Query（M言語）入門 - エクセルの神髄. https://excel-ubara.com/PowerQuery/.
```

素晴らしい!😍

一つ上の行を参照するM言語の式は、これですね。

```shell
= try #"PreviousStepName"{[Index]-1}[ColumnName] otherwise null
```

**try-catch が出来る**事を初めて知りました。お恥ずかしながら😅
**毎回、何か学びがあるんです。業務用にPromptをしていると**。

:::note info
ChatGPT や Microsoft Copilot にコードを書いてもらうと、最新のAPIなどが反映されてないことがあります。そのため、どうしてもドラフト感が否めません。謝罪メールなどドキュメント作成時は、ほぼそのまま使えますけどね。

ですが、**学び**があったります。そこからまた考えますよね?

「あら?例外処理的なものを書けたりするの?」

で、自分で動かすことで、学びが更に深まりますね。
:::

さて、Power Queryに戻って、試してみましょう。

- インデックス用の列を追加します
    - [列を追加します] - [インデックス列]
    - レコード間の位置、並びを指定するためのダミーデータです。今回は、Excelの並びのまま使って「年月」を連続で位置の特定ができるようにしています

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/339c5746-9237-6ebe-8411-0ce43c247d31.png)

- 必要に応じて、列名を変更します
    - 日本語での入力が手間なので...私は途中で使う名称は、適切な**英語の列名**にする事が多いです
    - ここでは「idx」とします

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/c2ecca73-fc26-f1e1-31fe-d6f517b63897.png)

- [カスタム列]を追加します

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/ae63a412-a6cb-33ba-dea0-613d9f969e8e.png)

- 式を入力します

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/0ff3c8b9-b9aa-9f3d-e52c-de119024d135.png)

```shell
try #"追加されたインデックス"{[idx]-1}[Column5] otherwise null
```
:::note Info
Copilot君が作ってくれたコードの中で、最初の「=」は、不要です。
:::

| 式の内の項目 | 説明 |
| --- | --- |
| #"追加されたインデックス" | Power Queryの一つ前の**ステップ名**です |
| [idx] | 追加したインデックスの**列名**です。レコードの並び・位置関係の参照先の列です |
| [Column5] | データを表示させたい列名です。まず`4月`を試してみました。4月から3月まで、繰り返します。 |

出来ました!

ちょっと遠いですが😅

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/d4ce0114-29e1-961f-f782-309e3169fb69.png)

これで、`Column5` と `4月`の文字列結合をすれば、`2024/4`月という文字列が作成できますね😊

- [カスタム列]を追加
- 年と月の文字列結合を実施

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/2a3ccc07-0a29-aa8f-6eba-6957c4091e41.png)

```shell
[4月] & "/" & [Column5] & "/01"
```

でけた!

注目すべきは**2行目**です。ここでは`2024/4月/01`という年月日が入った文字列が出来ています。`月`の文字が邪魔ですが、後で文字列置換にて消すことにしましょう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/add92ce7-9770-6d8a-d9a6-7236726003be.png)

残り、5月から、3月まで繰り返します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/cf8ae4b7-99ed-07e5-d78a-c8ad0678fa38.png)

頑張りました...
ここマジで大変です😭。一番時間がかかるところ。

`詳細エディター`の内容も書いておきます。詳細エディターでは、Power Queryで操作した手順が、M言語のコードになっていることを確認できて、編集も出来ます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/483b6a02-6242-4411-35f8-104932f9248a.png)

```shell
let
  ソース = Excel.Workbook(File.Contents("C:\Users\dahatake\OneDrive - Microsoft\デスクトップ\電力使用量.xlsx"), null, true),
  #"ナビゲーション 1" = ソース{[Item = "使用量", Kind = "Sheet"]}[Data],
  昇格されたヘッダー数 = Table.PromoteHeaders(#"ナビゲーション 1", [PromoteAllScalars = true]),
  変更された列タイプ = Table.TransformColumnTypes(昇格されたヘッダー数, {{"エネルギー使用量", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column17", Int64.Type}}, "ja"),
  削除された上位の行 = Table.Skip(変更された列タイプ, 1),
  #"変更された列タイプ 1" = Table.TransformColumnTypes(削除された上位の行, {{"エネルギー使用量", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}}),
  削除された列 = Table.RemoveColumns(#"変更された列タイプ 1", {"エネルギー使用量", "Column17"}),
  追加されたインデックス = Table.AddIndexColumn(削除された列, "インデックス", 0, 1, Int64.Type),
  名前が変更された列 = Table.RenameColumns(追加されたインデックス, {{"インデックス", "idx"}}),
  追加されたカスタム = Table.TransformColumnTypes(Table.AddColumn(名前が変更された列, "4月", each try #"追加されたインデックス"{[idx]-1}[Column5] otherwise null), {{"4月", type text}}),
  #"追加されたカスタム 1" = Table.AddColumn(追加されたカスタム, "Year-4月", each [4月] & "/" & [Column5] & "/01"),
  #"削除された列 1" = Table.RemoveColumns(#"追加されたカスタム 1", {"4月"}),

  #"追加されたカスタム 2" = Table.AddColumn(#"削除された列 1", "5月", each try #"追加されたインデックス"{[idx]-1}[Column6] otherwise null),
  #"追加されたカスタム 3" = Table.AddColumn(#"追加されたカスタム 2", "Year-5月", each [5月] & "/" & [Column6] & "/01"),
  #"削除された列 2" = Table.RemoveColumns(#"追加されたカスタム 3", {"5月"}),

  #"追加されたカスタム 4" = Table.AddColumn(#"削除された列 2", "6月", each try #"追加されたインデックス"{[idx]-1}[Column7] otherwise null),
  #"追加されたカスタム 5" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 4", "Year-6月", each [6月] & "/" & [Column7] & "/01"), {{"Year-6月", type text}}),
  #"削除された列 3" = Table.RemoveColumns(#"追加されたカスタム 5", {"6月"}),

  #"追加されたカスタム 6" = Table.AddColumn(#"削除された列 3", "7月", each try #"追加されたインデックス"{[idx]-1}[Column8] otherwise null),
  #"追加されたカスタム 7" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 6", "Year-7月", each [7月] & "/" & [Column8] & "/01"), {{"Year-7月", type text}}),
  #"削除された列 4" = Table.RemoveColumns(#"追加されたカスタム 7", {"7月"}),

  #"追加されたカスタム 8" = Table.AddColumn(#"削除された列 4", "8月", each try #"追加されたインデックス"{[idx]-1}[Column9] otherwise null),
  #"追加されたカスタム 9" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 8", "Year-8月", each [8月] & "/" & [Column9] & "/01"), {{"Year-8月", type text}}),
  #"削除された列 5" = Table.RemoveColumns(#"追加されたカスタム 9", {"8月"}),

  #"追加されたカスタム 10" = Table.AddColumn(#"削除された列 5", "9月", each try #"追加されたインデックス"{[idx]-1}[Column10] otherwise null),
  #"追加されたカスタム 11" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 10", "Year-9月", each [9月] & "/" & [Column10] & "/01"), {{"Year-9月", type text}}),
  #"削除された列 6" = Table.RemoveColumns(#"追加されたカスタム 11", {"9月"}),

  #"追加されたカスタム 12" = Table.AddColumn(#"削除された列 6", "10月", each try #"追加されたインデックス"{[idx]-1}[Column11] otherwise null),
  #"追加されたカスタム 13" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 12", "Year-10月", each [10月] & "/" & [Column11] & "/01"), {{"Year-10月", type text}}),
  #"削除された列 7" = Table.RemoveColumns(#"追加されたカスタム 13", {"10月"}),

  #"追加されたカスタム 14" = Table.AddColumn(#"削除された列 7", "11月", each try #"追加されたインデックス"{[idx]-1}[Column12] otherwise null),
  #"追加されたカスタム 15" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 14", "Year-11月", each [11月] & "/" & [Column12] & "/01"), {{"Year-11月", type text}}),
  #"削除された列 8" = Table.RemoveColumns(#"追加されたカスタム 15", {"11月"}),

  #"追加されたカスタム 16" = Table.AddColumn(#"削除された列 8", "12月", each try #"追加されたインデックス"{[idx]-1}[Column13] otherwise null),
  #"追加されたカスタム 17" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 16", "Year-12月", each [12月] & "/" & [Column13] & "/01"), {{"Year-12月", type text}}),
  #"削除された列 9" = Table.RemoveColumns(#"追加されたカスタム 17", {"12月"}),

  #"追加されたカスタム 18" = Table.AddColumn(#"削除された列 9", "1月", each try #"追加されたインデックス"{[idx]-1}[Column14] otherwise null),
  #"追加されたカスタム 19" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 18", "Year-1月", each [1月] & "/" & [Column14] & "/01"), {{"Year-1月", type text}}),
  #"削除された列 10" = Table.RemoveColumns(#"追加されたカスタム 19", {"1月"}),

  #"追加されたカスタム 20" = Table.AddColumn(#"削除された列 10", "2月", each try #"追加されたインデックス"{[idx]-1}[Column15] otherwise null),
  #"追加されたカスタム 21" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 20", "Year-2月", each [2月] & "/" & [Column15] & "/01"), {{"Year-2月", type text}}),
  #"削除された列 11" = Table.RemoveColumns(#"追加されたカスタム 21", {"2月"}),

  #"追加されたカスタム 22" = Table.AddColumn(#"削除された列 11", "3月", each try #"追加されたインデックス"{[idx]-1}[Column16] otherwise null),
  #"追加されたカスタム 23" = Table.TransformColumnTypes(Table.AddColumn(#"追加されたカスタム 22", "Year-3月", each [3月] & "/" & [Column16] & "/01"), {{"Year-3月", type text}}),
  #"削除された列 12" = Table.RemoveColumns(#"追加されたカスタム 23", {"3月"})


in
  #"削除された列 12"
```

## 5. クリーンアップ - 最初のクエリー
年月以外の列を全て削除します。

やったこと:
- 列名変更
- 1行目削除
- (残った)1行目だけ保持
- 全ての列のデータ型を`テキスト`に
- 全ての`月`の文字を`(空文字)`に置換

完成です!

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/f76c65bd-7f30-1e2b-eb41-bd2061c8e59f.png)

## 6. 2つ目のクエリー作成

「え?データ入っていないと意味なくね?」となりますよね。その後の手順もざっくりと書いておきますね。

- クエリーを複製
    - 同じデータソースを使う際に、途中までの処理をコピペできるので超絶便利です

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/74552131-fa3a-9348-99fc-f2d60af53ca1.png)

- クエリの設定の、最初の7ステップまでを保持して、それ以外は`X`を押して削除
    - どのステップまでが、2つめのクエリーで使えるかを、データのプレビューを見ながら決めます

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/d169149e-c786-3f68-df98-4548a763dc61.png)

削除後の画面:

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/de704b44-9d72-c95d-b4bc-4cca8c073e78.png)

- 列名変更
    - 一つ目のクエリーと同じにする

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/1af6db07-483a-1d5d-2fae-0c5593b8e383.png)


- タイトル行である`上位2行`を削除。値のレコードだけにする

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/71a05f4e-4cb8-b625-16b9-86fb621d68f9.png)


- 「計」とNULLのレコード削除
    - `時間区分`の列のフィルターを選択して、`計`と`(null)`のチェックをはずす

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/65dc3785-d5b6-bd09-b4f4-824608b44eb1.png)

フィルター後:

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/3bf153d1-e9ac-8a08-5c40-81466843fe2b.png)

- `電力会社`のNULLを埋めます
    - [変換]のメニューに移動して、[フィル]-[下方向]を選択します

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/baaf6b6e-2306-ebf5-e75a-0a404ffc158e.png)

NULL埋めた後:

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/c5d4f978-fd5f-0f71-21fc-585908907de8.png)

- 数量のデータ型を`数値`に

これが2つ目の数量のクエリーの最終系です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/1600c41e-442d-9e52-4fb6-415d3595ab4c.png)


で、2つのクエリーをマージします。今回の場合は**アペンド**ですね。

- [クエリーのアペンド] - [新規としてクエリーをアペンド]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/0d345385-d7a8-3e47-5b31-3ec7e6ce9c28.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/5721c672-338a-bcd2-0ff4-0e2f3e63ad5a.png)

さー! 一つになりましたよ!

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/22ed74bc-dc4a-efe1-4316-ddc4d98380fb.png)

クリーンアップしていきましょう。

- 1行目をヘッダーに
- 列名を変更

そしたら..
数値以外の列を選択して。
- [その他の列をピボット解除]

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/ddd28d37-926d-85ef-3c26-150249e5d09c.png)

はいーーーー!!!😍

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/bf640ad0-3eea-6b3e-9717-8ae45b82740a.png)


更にクリーンアップ

- 列名変更
- データ型変更
    - 年月の列は`日付`に出来ますね

最終系です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108143/ba9e545a-fcd6-b931-0bd5-745ccc992531.png)

# 最後に

今回は**元のExcelファイルには一切変更をしていません**。
勿論、元のExcelファイルのデータを直すもの手段かもしれません。ですが、**既存の業務が走っている**のです。データ加工でのちょっとしたテクニックで、

『あ。そのExcelのファイルは、xxxというOneDrive for Businessのフォルダーにアップロードしておいて。

で、毎月Excelに直接データ入れてね。それでOKよ』

で、話が済むんです。**既存の業務フローを一切変える必要はありません**。

ちょっとした、Window関数的なものの使い方。

皆さんの業務にお役に立つ事を祈ります!