Excel
PowerBI
PowerQuery

構造化されていない表からレポートを作成するには

構造化されていないデータ/クロス集計されている表などはそのまま読み込んでも期待するレポートは作成できません。なので、Power BI Desktop や Excel の クエリ エディターで変換加工し、グラフやチャート表示に適する状態にする必要があるのです。特に"行と列の入れ替え" や "ピボットの解除" は基本的なテクニックなので理解しておきたいものですね。

元のデータ

01.png
クロス集計されていると人は見やすくてよいのですが、カテゴリーごとであるとか担当ごとであるとかでフィルターを適用したグラフなどを簡単に構成できるように変換してみます。

ソース
売上集計,,,,,,,
,,,,,,,
,東京本社,,,大阪支社,,北九州支社,
年,調味料,菓子類,乳製品,調味料,牛乳,調味料,菓子類
2014,"¥799,700","¥533,700","¥481,900","¥529,600","¥218,800","¥686,100","¥278,900"
2015,"¥363,800","¥550,000","¥270,200","¥475,000","¥215,900","¥322,100","¥109,900"
2016,"¥521,600","¥697,300","¥379,200","¥460,000","¥215,400","¥182,600","¥174,800"

Power BI Desktop を使って

Power BI Desktop で説明しますが、Excel でも同じ作業を実施できます。Excel の場合はデータの取得と変換を使用のこと

データを取得

ソースとなるデータは Excel ワークシートにあるものとして
02.png

ナビゲーター

Excel ワークブック の 対象となるデータが含まれる ワークシート を選択し、[編集]、で、クエリ エディターを起動
03.png

上位の行の削除

取り込まれたデータはテーブルとして取得できていますが、不要な "行" が含まれているので削除
このケースでは先頭の行だけですが、不要な列や行がある場合は削除し必要な部分だけ残るようにします。
04.png

行と列の入れ替え

クロス集計の階層化された 列見出し は 行と列の "入れ替え" でフィールドの値として変換
06.png

1行目をヘッダーとして使用

行と列の "入れ替え"で 列見出しだった "年" が 値として1行目になるので、ヘッダーとして使用
07.png

列名を変更

列名をダブルクリックなどして変更
08.png

フィル

”担当” 列を選択し、下方向に”フィル”
09.png

ピボットの解除

”担当” ”区分” 列を選択し ”その他の列のピボット解除”で、列見出しとなっている ”年”を値に変換
10.png

列名の変更

ピボット解除で変換された列名を変更
11.png

データ型の変更

元のデータは金額を表しているので、適するデータ型に変更
12.png

レポート作成

お好きなように
13.png

コードなど

MCode
let
    FullName = "D:\Data\Demo.xlsx"
    Source = Excel.Workbook(File.Contents(FullName), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    RemovedTopRows = Table.Skip(Sheet1_Sheet,2),
    TransposedTable = Table.Transpose(RemovedTopRows),
    PromotedHeaders = Table.PromoteHeaders(TransposedTable, [PromoteAllScalars=true]),
    ChangedType1 = Table.TransformColumnTypes(
        PromotedHeaders,
        {
            {"Column1", type text}
          , {"年", type text}
          , {"2014", Int64.Type}
          , {"2015", Int64.Type}
          , {"2016", Int64.Type}
        }
    ),
    RenamedColumns = Table.RenameColumns(
        ChangedType1
       ,{
            {"Column1", "担当"}
          , {"年", "区分"}
       }
    ),
    FilledDown = Table.FillDown(RenamedColumns,{"担当"}),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(
        FilledDown
      , {"担当", "区分"}
      , "Attribute"
      , "Value"
    ),
    RenamedColumns1 = Table.RenameColumns(
        UnpivotedOtherColumns
       ,{
            {"Attribute", "年"}
          , {"Value", "売上"}
        }
    ),
    ChangedType2 = Table.TransformColumnTypes(
        RenamedColumns1
       ,{
            {"売上", Currency.Type}
        }
    )
in
    ChangedType2

その他