背景

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

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

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

時間
種別
数量

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

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

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

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

作戦

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

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

手順

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

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

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

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

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

年が2行目
月が3行目

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

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

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

3. データ型を整理

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

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

全ての列を選択します

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

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

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

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

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

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

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

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

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

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

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

Prompt:

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

出力文字列:

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言語の式は、これですね。

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

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

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

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

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

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

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

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

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

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

式を入力します

try #"追加されたインデックス"{[idx]-1}[Column5] otherwise null

Copilot君が作ってくれたコードの中で、最初の「=」は、不要です。

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

出来ました!

ちょっと遠いですが😅

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

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

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

でけた!

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

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

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

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

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行目だけ保持
全ての列のデータ型をテキストに
全ての月の文字を(空文字)に置換

完成です!

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

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

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

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

削除後の画面:

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

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

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

フィルター後:

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

NULL埋めた後:

数量のデータ型を数値に

これが2つ目の数量のクエリーの最終系です。

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

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

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

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

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

そしたら..
数値以外の列を選択して。

[その他の列をピボット解除]

はいーーーー!!!😍

更にクリーンアップ

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

最終系です。

最後に

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

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

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

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

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

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