(2022/02/18に記述しました)
#14のように、まとめようとするエクセルファイルの形式が異なっている場合、どのようにまとめていくかを考えます。
スライドは、Speaker Deckを参照してください。
元のファイルと目標のデータテーブル
文部科学省の学校基本調査のデータをe-Statからダウンロードして使っていきます。
今回使うエクセルファイルを見てみると、以下のようなものがありました。
1つ目はA1から始まる表になってて使いやすそうです。その次は、分類が増えて見栄えもよくした感じで、最後のはやたら空の列や行を入れてます。これを以下のようなデータテーブルに変換したいと思います。
1.フォルダーパスをパラメータに設定
対象のエクセルファイルが入ったフォルダーのパスを、パラメータに設定します。
2.フォルダー内のファイルを表示
フォルダー内のファイルを表示するクエリーを作成します。
let
Source = Folder.Files(FolderPath)
in
Source
3.サンプルファイルを作成
2で表示された1番目のエクセルファイル(1976.xls)をサンプルファイルとして登録します。
let
Source = Folder.Files(FolderPath){[Name="1976.xls"]}[Content]
in
Source
4.サンプルファイルへのパラメータを作成
サンプルファイルを加工するクエリを作る前に、パラメータを作成します。
このパラメータは、サンプルファイルを渡すためのものなので、種類はバイナリにします。
5.サンプルファイルの変換クエリを作成
変換作業を行う前に、サンプルファイルを読み込む部分を書きます。
let
Source = Excel.Workbook(Parameter1976),
WorkSheet = Source{0}[Data]
in
WorkSheet
- Parameter1976 →パラメータで入力ファイルを指定
- {0} → ここでは、データの入っているシートは1番目のものを指定
- [Data] → Data(シート内のデータ)とName(シート名)の2つの項目のうちDataを指定
6.変換作業を記述していく
サンプルファイルを読み込んだ後の処理をGUIで操作していきます。
let
Source = Excel.Workbook(Parameter1976){0}[Data],
PromotedHeaders =
Table.PromoteHeaders(Source,
[PromoteAllScalars=true]
),
ChangedType =
Table.TransformColumnTypes(
PromotedHeaders,{
{"区分", type text},
{"国立", Int64.Type},
{"公立", Int64.Type},
{"私立", Int64.Type}
}
),
UnpivotedOtherColumns =
Table.UnpivotOtherColumns(
ChangedType,
{"区分"},
"属性",
"値"
),
// エラー値を0に置換
ReplacedErrors =
Table.ReplaceErrorValues(
UnpivotedOtherColumns,
{{"値", 0}}
)
in
ReplacedErrors
7.関数の作成
出来上がったクエリを右クリックし、関数の作成を選択し、関数を作ります。
8.各ファイルに関数を使ってみる
最初に作成したフォルダー内のファイルを表示させるクエリに、関数を適用する項目を追加します。
let
Source = Folder.Files(FolderPath),
// Excelからテーブルを取得
Step1 = Table.TransformColumns(
Source,
{
"Content",
ConvertFx1976
}
)
in
Step1
1行目は正しく変換されていますが、2行目はエラーとなっています。
9.別の関数を作成
最初の関数では処理できないので、新しく関数を作成するため、３～７を行います。
ここで作成したクエリは以下のようなものでした。
let
// エクセルの最初のシートを開く
Source = Excel.Workbook(Parameter1977){0}[Data],
ChangedType =
Table.TransformColumnTypes(
Source,
{
{"Column1", type text}
}
),
// 有効な行を選択
FilteredRows =
Table.SelectRows(
ChangedType,
each (
[Column1] = "区分" or
[Column1] = "男女ともにいる学校" or
[Column1] = "男のみの学校" or
[Column1] = "女のみの学校" or
[Column1] = "生徒のいない学校"
)
),
// 1行目をヘッダーに
PromotedHeaders =
Table.PromoteHeaders(
FilteredRows,
[PromoteAllScalars=true]
),
FilteredRows1 =
Table.SelectRows(
PromotedHeaders,
each ([区分] <> "区分")
),
// 不要な列を削除
RemovedOtherColumns =
Table.SelectColumns(
FilteredRows1,
{"区分", "国立", "公立", "私立"}
),
// データ型を指定
ChangedType1 =
Table.TransformColumnTypes(
RemovedOtherColumns,
{
{"区分", type text},
{"国立", Int64.Type},
{"公立", Int64.Type},
{"私立", Int64.Type}
}
),
// ピボット解除
UnpivotedOtherColumns =
Table.UnpivotOtherColumns(
ChangedType1,
{"区分"},
"属性",
"値"
),
// エラー値を0に置換
ReplacedErrors =
Table.ReplaceErrorValues(
UnpivotedOtherColumns,
{{"値", 0}}
)
in
ReplacedErrors
10.再度関数を当ててみる
クエリーを書き換えて、もう一度確認してみます。
let
Source = Folder.Files(FolderPath),
// Excelからテーブルを取得
Step1 = Table.TransformColumns(
Source,
{
"Content",
each
try ConvertFx1976(_)
otherwise ConvertFx1977(_)
}
)
in
Step1
まだ1行、エラーが発生しています。
11.3つ目の関数を作成して当ててみる
３～７を繰り返して関数を作ります。
クエリに新たに作った関数を追加して適用させます。
let
Source = Folder.Files(FolderPath),
// Excelからテーブルを取得
Step1 = Table.TransformColumns(
Source,
{
"Content",
each
try ConvertFx1976(_)
otherwise try ConvertFx1977(_)
otherwise ConvertFx2008(_)
}
)
in
Step1
12.テーブルを結合させて完成
クエリの続きを記述し、完成です。
let
Source = Folder.Files(FolderPath),
// Excelからテーブルを取得
Step1 = Table.TransformColumns(
Source,
{
"Content",
each
try
ConvertFx1976(_)
otherwise try
ConvertFx1977(_)
otherwise
ConvertFx2008(_)
}
),
// ファイル名をエクセル表に追加して結合
Step2 = Table.AddColumn(
Step1,
"Workbooks",
(x)=>
Table.AddColumn(
x[Content],
"Year",
each Number.FromText(Text.Select(x[Name],{"0".."9"})),
Int64.Type
)
),
Step3 = Table.Combine(Step2[Workbooks])
in
Step3
このデータを使って以下のグラフを作成しました。
注意点
今回は、作成した関数が正常化エラーかで判断していますが、エラーにならないけど正常値を返さないパターンもあると思います。その場合は、どちらでも正常値になるようにクエリ内の処理を追加するか、エラーをわざと発生させるかの対応が必要になります。