LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@dahatake(Daiyu Hatakeyama)inMicrosoft

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

Last updated at Posted at 2024-07-30

背景

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

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

image.png

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

  • 時間
  • 種別
  • 数量

image.png

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

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

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

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

作戦

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

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

手順

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

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

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

image.png

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

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

  • 年が2行目
  • 月が3行目

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

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

image.png

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

image.png

3. データ型を整理

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

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

  • 全ての列を選択します

image.png

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

image.png

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/

image.png

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の並びのまま使って「年月」を連続で位置の特定ができるようにしています

image.png

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

image.png

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

image.png

  • 式を入力します

image.png

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

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

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

出来ました!

ちょっと遠いですが😅

image.png

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

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

image.png

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

でけた!

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

image.png

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

image.png

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

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

image.png

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

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

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

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

image.png

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

image.png

削除後の画面:

image.png

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

image.png

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

image.png

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

image.png

フィルター後:

image.png

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

image.png

NULL埋めた後:

image.png

  • 数量のデータ型を数値に

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

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

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

image.png

image.png

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

image.png

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

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

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

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

image.png

はいーーーー!!!😍

image.png

更にクリーンアップ

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

最終系です。

image.png

最後に

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

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

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

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

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

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

3
1
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?