5章: カスタム列と関数

この章では、Power Queryでカスタム列を追加する方法や関数の作成・使用方法について学びます。また、M言語の基本やクエリのパラメータ化、エラー処理とデバッグについても触れます。

カスタム列の追加

カスタム列を追加することで、既存の列から新しい列を生成したり、条件に基づいた値を持つ列を作成できます。

例えば、次のような商品テーブルがあるとします。

このテーブルに、「価格」列から消費税（10%）を含めた価格を表示するカスタム列「税込価格」を追加しましょう。クエリエディタで「追加列」タブをクリックし、「カスタム列」を選択して、「税込価格」という名前のカスタム列を作成します。式には、「価格 * 1.1」と入力します。

結果は以下のようになります。

関数の作成と使用(参考)

Power Queryでは、独自の関数を作成して繰り返し使用することができます。関数は、特定の処理をまとめて再利用するための機能で、クエリの処理を効率化できます。

例えば、消費税を計算する関数を作成しましょう。クエリエディタで「ホーム」タブをクリックし、「関数」を選択し、「新しい関数」を作成します。関数名を「CalcTax」とし、次のM言語のコードを入力します。

(value as number, rate as number) => value * (1 + rate)

これにより、「CalcTax」関数が作成されます。この関数を使用して、「価格」列に消費税を適用する新しい列を作成しましょう。クエリエディタで「追加列」タブをクリックし、「カスタム列」を選択します。「税込価格」という名前のカスタム列を作成し、式に「CalcTax([価格], 0.1)」と入力します。これにより、消費税が10%の場合の税込価格が計算されます。

結果は以下のようになります。

M言語の基本(参考)

Power Queryでは、M言語という独自のプログラミング言語が使われています。M言語は関数型言語で、クエリの変換ステップを記述するために使用されます。M言語の構文はシンプルで、既存の関数を組み合わせて新しい関数を作成することができます。

M言語では、変数や関数を「let」および「in」のキーワードを使用して定義します。例えば、以下のコードは、「x」という変数に数値のリストを代入し、その合計値を計算するM言語のコードです。

let
  x = {1, 2, 3, 4, 5},
  y = List.Sum(x)
in
  y

M言語を考慮してすすめる場合は以下の解説ページを事前にお読みください。

M言語講座

初級者向けのM言語学習記事です。 1章: M言語の概要と基本構文 M言語とは Power QueryでのM言語の役割 式の構成 変数の定義 コメントの書き方 関数の呼び出し 演習問題 2章: データ型とリテラル 数値型 文字列型 日付型と時...

www.helpaso.net

2023.04.05

クエリのパラメータ化

クエリのパラメータ化によって、データの取得や変換において一部の値を動的に変更することができます。例えば、異なる通貨レートを適用するための為替レートパラメータや、期間によって変化するデータを抽出するための日付パラメータなどが考えられます。

パラメータを作成するには、クエリエデ
ィタで「ホーム」タブをクリックし、「パラメータ」を選択し、「新しいパラメータ」を作成します。例えば、「CurrencyRate」という名前の数値型のパラメータを作成し、初期値を「110」とします。これにより、為替レートを示すパラメータが作成されます。

このパラメータを使用して、「価格」列を別の通貨に変換する新しい列を作成しましょう。クエリエディタで「追加列」タブをクリックし、「カスタム列」を選択します。「USD価格」という名前のカスタム列を作成し、式に「[価格] / CurrencyRate」と入力します。これにより、「価格」列が為替レートによってUSDに変換されます。

結果は以下のようになります。

エラー処理とデバッグ

Power Queryでデータ変換を行う際、エラーが発生することがあります。エラーの原因を特定し、適切な処理を行うことが重要です。

エラーが発生したセルには、通常、「Error」と表示されます。このエラーを修正するには、まず、該当するセルにカーソルを合わせて、エラーメッセージを確認します。次に、適切な修正を行います。例えば、空白のセルに対して数値演算を行おうとした場合、エラーが発生します。この場合、空白セルを0に変換することでエラーを解消できます。

また、M言語のコードを直接編集することで、デバッグを行うことができます。コード内のエラー箇所を特定し、修正することで問題を解決できます。

最後に、データの品質を向上させるために、クエリエディタの「データの品質機能」を活
用しましょう。「データの品質機能」には、データのプロファイリングや検証、修復機能が含まれており、これらを利用することでデータ変換中のエラーや不整合を効率的に解決できます。

例えば、データのプロファイリング機能を使うことで、列の値の分布や、欠損値、重複値などの問題を特定し、適切な修正を行うことができます。また、検証ルールを設定することで、特定の条件に従ったデータの正確性を保証できます。

データの修復機能を利用して、エラーが発生したデータを修正したり、エラーが発生したレコードを除外することも可能です。これにより、データの品質を維持し、正確な分析を実施することができます。

このように、Power Queryを活用してデータの変換や整形を行う際には、エラー処理やデバッグ、データの品質機能を駆使し、データの整合性を確保することが重要です。

この章では、カスタム列と関数の作成・使用方法やM言語の基本、クエリのパラメータ化、エラー処理とデバッグについて学びました。これらの知識を活用して、より効率的で柔軟なデータ変換を行い、データ分析を成功させましょう。

第5章 演習問題

以下の演習問題を解いて、第5章で学んだ内容を確認しましょう。

1. 以下のデータセットがあります。
   

   氏名	年齢	都道府県	購入金額（円）
   山田 太郎	25	東京都	10000
   佐藤 次郎	31	神奈川県	8000
   田中 花子	29	東京都	12000

   消費税率が10%であるとして、購入金額に消費税を加算した「税込金額」列を追加してください。カスタム列を利用して計算し、正しい答えを求めてください。

2. 前述のデータセットに対して、「年齢」列を基に以下の年代別カテゴリーに分ける新しい「年代」列を作成してください。
   • 20代
   • 30代
   • 40代
   • 50代以上

   条件付き列を使用して、正しいカテゴリーを割り当ててください。

3. 次のM言語コードを分析し、どのような処理が行われているか説明してください。(参考)

       let
         Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
         ChangedType = Table.TransformColumnTypes(Source,{{"氏名", type text}, {"年齢", Int64.Type}, {"都道府県", type text}, {"購入金額", Int64.Type}}),
         AddedCustom = Table.AddColumn(ChangedType, "税込金額", each [購入金額] * 1.1)
       in
         AddedCustom
   

4. 前述のデータセットを使用して、購入金額をUSD（米ドル）に変換したいとします。為替レートをパラメータとして設定し、新しい「購入金額（USD）」列を追加してください。このパラメータは、1USDあたりの日本円の為替レートを表しています。パラメータを利用して、正しい答えを求めてください（為替レートの例：1USD = 110円）。(参考)
5. 以下のエラーが発生するM言語コードがあります。

       let
         Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
         ChangedType = Table.TransformColumnTypes(Source,{{"氏名", type text}, {"年齢", Int64.Type}, {"都道府県", type text}, {"購入金額", Int64.Type}}),
         AddedCustom = Table.AddColumn(ChangedType, "税込金額", each [購入金額] * 1.1),
         AddedColumnUSD = Table.AddColumn(AddedCustom, "購入金額（USD）", each [購入金額] * ExchangeRate)
       in
         AddedColumnUSD
   

   このエラーの原因を特定し、修正してください。また、修正によってエラーが解決されることを説明してください。(参考)

これらの演習問題を通じて、カスタム列と関数の作成・使用方法やM言語の基本、クエリのパラメータ化、エラー処理とデバッグについて学んだ内容を復習し、理解を深めましょう。