背景
入力用の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つのクエリーをマージします。今回の場合はアペンドですね。
- [クエリーのアペンド] - [新規としてクエリーをアペンド]
さー! 一つになりましたよ!
クリーンアップしていきましょう。
- 1行目をヘッダーに
- 列名を変更
そしたら..
数値以外の列を選択して。
- [その他の列をピボット解除]
はいーーーー!!!😍
更にクリーンアップ
- 列名変更
- データ型変更
- 年月の列は
日付
に出来ますね
- 年月の列は
最終系です。
最後に
今回は元のExcelファイルには一切変更をしていません。
勿論、元のExcelファイルのデータを直すもの手段かもしれません。ですが、既存の業務が走っているのです。データ加工でのちょっとしたテクニックで、
『あ。そのExcelのファイルは、xxxというOneDrive for Businessのフォルダーにアップロードしておいて。
で、毎月Excelに直接データ入れてね。それでOKよ』
で、話が済むんです。既存の業務フローを一切変える必要はありません。
ちょっとした、Window関数的なものの使い方。
皆さんの業務にお役に立つ事を祈ります!