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

元のデータ

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

ソース

売上集計,,,,,,,
,,,,,,,
,東京本社,,,大阪支社,,北九州支社,
年,調味料,菓子類,乳製品,調味料,牛乳,調味料,菓子類
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 ワークシートにあるものとして

ナビゲーター

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

上位の行の削除

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

行と列の入れ替え

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

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

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

列名を変更

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

フィル

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

ピボットの解除

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

列名の変更

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

データ型の変更

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

レポート作成

お好きなように

コードなど

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

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