はじめに
Excel はセル単位で自由度の高い表を作ることができる反面、分析データとして扱うには難しい表ができてしまうことがあります。よくあるのが、複数行でヘッダーを表現してしまっている場合で、分析可能なデータにするためには、ヘッダーを1行にまとめる必要があります。さらに、これをフォルダから複数ファイルまとめて取り込みを行いたいというケースについて、最近質問を受け、対処方法をまとめましたので、ご覧いただければと思います。
取り込み対象の複数行ヘッダー Excel ファイル
取込対象とする Excel ファイルのサンプルは以下のような形式です
フォルダ構成
OneDrive もしくは SharePoint Online の同一フォルダ上に同じフォーマットの Excel ファイルが格納
Excel ファイル
ヘッダーが複数行で構成されている Excel ファイル(1 行目はセル結合されている)
このファイルが複数、同一フォーマットで存在し、上図フォルダ内にあると想定します。
取り込み手順
今回、Power BI Desktop にて取込を実施します。
-
「データを取得」にて「SharePoint フォルダー」を指定
-
「サイト URL」に OneDrive のルート URL を入力 → 認証を求められたら認証情報入力
-
「データの変換」を押下 → Power Query エディタが立ち上がり、サイト URL 配下のファイル全てがリスト形式で表示される
-
列「Folder Path」をフィルタし、該当フォルダだけに絞り込む
※補足①「Name」などで絞り込んでも良いですが、同一名称のファイルなどが引っ掛かったり、後から追加されたファイルが対象にならなかったりするので、「Folder Path」で絞り込んだ方が良いです。
※補足② OneDrive や SharePoint Online のサイト上のファイルが多いと絞り込みに時間がかかるため、SharePoint Online 上に新しいサイトを作るのも良いかと思います。
-
列「Content」を展開する → Excel ファイルの中身が展開され、複数ファイルが結合(Union)された状態になる
-
「ファイルの結合」→ そのまま OK
ヘルパークエリが自動作成され、以下のような状態になる
-
ヘルパークエリ内の「サンプル ファイルの変換」をクリック
→ 1つ目のファイルをサンプルとして変換定義が作成されているので、ここを修正するのがポイント
-
ヘッダーの整形をやり直すため、「クエリの設定」の「適用したステップ」の「昇格されたヘッダー数」を削除
-
リボンの「変換」タブの「入れ替え」を押下し、行と列を入れ替える
→ 行の結合はできないが、列の結合は行えるため、ヘッダー行を結合するため、一旦行列入替をおこなう
-
列「Column1」を右クリックし、「フィル」→「下へ」を選択
→ これにより Excelファイル上で、セル結合で穴あきになっていた箇所に値が入る
-
Ctrl キーを押しながら、列「Column1」「Column2」を選択し、右クリックし、「列のマージ」を選択
-
列のマージダイアログにて、区切り文字と新しい列名を入力
※例では、区切り文字に「-」、新しい列名はそのまま
-
リボンの「変換」タブの「入れ替え」を押下し、行と列を元に戻す
-
リボンの「変換」タブの「1行目をヘッダーとして使用」を押下
-
一番左の列の名前を適切なものへ変更
ここまでで、サンプルファイルによる変換ロジックの定義が完了です。
-
「クエリ」の「クエリ１」を選択 → エラーが出ている
→ 最初に取り込んだ時、型の変換を自動で行っていた名残が残っており、これまでの工程で列名が変更されたことによってエラーが出ています。
-
「クエリの設定」→「適用したステップ」で「変更された型」を削除
-
手動で各列の型を設定
-
「Source.Name」 から年を抽出 (本題と関係無いため、手順は省略します)
-
リボンの「ホーム」タブの「閉じて適用」を押下して取り込みを行う
-
取り込んだあとは自由にビジュアルを作ってください
まとめ
複数行でヘッダーが構成されている場合でも、Power Query 側で工夫して分析用データに加工することが可能です。また、フォルダ取込の場合は、ヘルパークエリの「サンプル ファイルの変換」に変換ロジックを作成できるので、こちらを加工することで、複数ファイルに対応することが可能です。今回はシンプルな構成の Excel を使用しましたが、ヘッダーの構成によっては変換手順が変わるかと思いますので、お手持ちの様々な Excel で試してみていただければと思います。