PowerQueryエディタでデータ編集 - aktnk/til GitHub Wiki

列の分割

ある列のデータが(カテゴリID)-(製品番号)-(サイズ)-(色ID)で構成されており、そのデータを(カテゴリID)、(製品番号)、(サイズ)、(色ID)の４つの列に分割する

  1. その列を選択し、[変換]タブ>[列の分割]>[区切り記号による分割]を選択
  2. [区切り記号による分割]のウインドウで、下記を設定し[OK]を押下
    • 区切り記号：-
    • 分割：区切り記号の出現ごと
    • [詳細設定オプション]>分割の方向：列
    • 分割後の列数:4
  3. 分割後の列名をカテゴリID、製品番号、サイズ、色IDに変更

日付文字列の統一

ある列に(日付)データが入っているが、行によって日付フォーマットが異なっているので、フォーマットを統一する 但し、(日付)データの違いを判定できる列データ（例えば、(国)等）が必要

  1. 列の分割を利用し(日付)データを年・月・日の各構成要素に分割する
    • (日付)データは再構成するのでここでは一旦分割して無くなってもOK
  2. [列の追加]タブ>[カスタム列]を選択
  3. [カスタム列]のウインドウで、下記を設定し[OK]を押下
    • 新しい列名：日付_年月日
    • カスタム列の式： 
      = if [国] = "USA" then #date([日付.3],[日付.1],[日付.2]) else #date([日付.3],[日付.2],[日付.1])
      上記は、USAでは元の日付データが(月)/(日)/(年)、それ以外では(日)/(月)/(年)でとなっていた場合の例
  4. 日付_年月日のデータ種別アイコンをクリックし、日付を選択する

例からの列

数値で入力されている(サイズ)データから、S・M・Lで分類した列データを作成する

  1. (サイズ)列を選択した状態で、[列の追加]タブ>[例からの列]>[選択範囲から]を選択
  2. [例から列を追加する]が表示される空の列が表示されるので、列名をサイズIDに変更
  3. 空になっているデータ行を１つ選択し、(サイズ)の値から対応しているサイズIDとしてS/M/Lから1つ入力する
  4. 上記入力を何度か繰り返すことで、入力していない行のデータがnullでなく(正しそうなデータ)が自動設定されたら[OK]を押下
  5. [クエリの設定]>[適用したステップ]欄の最後にある[追加された条件列]の歯車アイコンを押下
  6. [条件列の追加]ウインドウが表示されるので、自動設定された各条件を確認し、設定内容に間違いがあれば修正し[OK]を押下

データの正規化

販売実績データに含まれる商品データから商品マスタを作成する 具体的には、販売実績データに注文ID,注文日,注文数,…,商品ID,商品名,単価,…があり、ここから、商品ID,商品名,単価からなる商品マスタとその他の注文実績を作成する

  • 商品マスタの作成
    1. [クエリ]に表示される販売実績を右クリックし、[参照]を選択
    2. [クエリの設定]に表示される[プロパティ]>[名前]を、商品マスタに変更
    3. [クエリ]で商品マスタを選択し、[ホーム]タブ>[列の選択]>[列の選択]を選択
    4. [列の選択]ウインドウが表示されるので、[すべての列の選択]のチェックを外し、商品マスタに残す商品ID,商品名,単価にチェックを入れ[OK]を押下
    5. 商品IDを選択し、[ホーム]タブ>[行の削除]>[重複の削除]を選択
  • 注文実績の作成
    1. [クエリ]に表示される販売実績を右クリックし、[参照]を選択
    2. [クエリの設定]に表示される[プロパティ]>[名前]を、受注実績に変更
    3. [クエリ]で受注実績を選択し、[ホーム]タブ>[列の選択]>[列の選択]を選択
    4. [列の選択]ウインドウが表示されるので、商品名,単価にチェックを外し[OK]を押下
  • 販売実績の読み込みを設定を無効化し、データを読み込む
    1. [クエリ]に表示される販売実績を右クリックし、[読み込みを有効にする]のチェックを外す
    2. [閉じて適用]を選択 →商品マスタ,注文実績が読み込まれる

データの余分な空白を削除する

データの先頭や末尾に余分な空白が含まれている場合、余分な空白を取り除く

  1. 該当の列を選択し、[変換]タブ>[書式]>[トリミング]を選択