Power Query へそのゴマ 第５章 コンテキストの維持と管理

この章では、Power Queryでデータを統合・変換する際に「コンテキスト」を維持・管理する方法について学びます。コンテキストとは、データの出所や意味を示す追加情報を指し、データを結合する際や分析に役立ちます。たとえば、ファイル名やシート名、データ取得元のディレクトリ構造などの情報をクエリ内で保持することで、データの整合性と追跡性が向上します。この章では、コンテキストを効率的に管理するテクニックや実践的なアプローチを紹介します。

5.1. ファイル名やシート名を利用したコンテキストの維持

Power Queryでファイルやシートごとに異なるデータを取り扱う際、ファイル名やシート名をコンテキストとして保持することで、データの出所を明確にすることができます。

5.1.1. ファイル名やシート名を取得する方法

• ファイル名を取得: Power Queryで「ファイルから」データを取得する際、各ファイルの名前を取得して「ソース」列として保持する方法を解説します。これにより、複数のファイルから統合したデータの出所を明確にします。

let
    // フォルダ内の全ファイルを取得
    Source = Folder.Files("C:\Data\MonthlySales"),
    
    // 必要なファイル情報を選択
    SelectColumns = Table.SelectColumns(Source, {"Name", "Content"}),
    
    // ファイルごとにデータを展開
    ExpandedData = 
        Table.AddColumn(
            SelectColumns, 
            "Data", 
            each Excel.Workbook([Content], true)  // 1行目をヘッダーに
        ),
        
    // ファイル名とシート名をコンテキスト情報として保持
    ExpandedFileNames = 
        Table.ExpandTableColumn(
            ExpandedData, 
            "Data", 
            {"Name", "Data"}, 
            {"SheetName", "Data"}
        ),
        
    // データの展開
    FinalData = 
        Table.ExpandTableColumn(
            ExpandedFileNames, 
            "Data", 
            {"Sales", "Date"}
        ),
        
    #"Changed Type" = 
        Table.TransformColumnTypes(
            FinalData,
            {
                {"SheetName", type text}, 
                {"Sales", type number}, 
                {"Date", type date}
            }
        )
in
    #"Changed Type"

コンテキスト情報を使って、特定のファイル名やシート名に基づいてデータをフィルタリングすることができます。

また、ファイル名やシート名が明確になることで、データの追跡が容易になります。特に複数のソースから統合する際、データの整合性を保ちながら分析に役立てることができます。

5.2. コンテキストを利用したデータ結合と処理

Power Queryでのデータ結合時に、ファイルやシートのコンテキスト情報を活用することで、結合精度とデータ処理の効率を向上させます。

5.2.1. コンテキストを利用したマージの活用

データの結合（マージ）操作の際に、ファイルやシートごとの属性を追加することで、データの整合性を保ちます。たとえば、顧客データと売上データを統合する際に地域情報などを保持し、分析の視点を追加します。

let
    CustomerData = Excel.Workbook(File.Contents("C:\Data\CustomerData.xlsx"), null, true),
    SalesData = Excel.Workbook(File.Contents("C:\Data\SalesData.xlsx"), null, true),
    
    // カスタム列で地域情報を追加
    CustomerWithRegion = 
        Table.AddColumn(
            CustomerData, 
            "Region", 
            each "North America"
        ),
    
    // 顧客データと売上データを「CustomerID」で結合
    MergedData = 
        Table.NestedJoin(
            SalesData,           // table1 as table
            {"CustomerID"},      // key1 as any
            CustomerWithRegion,  // table2 as any
            {"CustomerID"},      // key2 as any
            "CustomerInfo",      // newColumnName as text
            JoinKind.Inner       // optional joinKind as nullable number
        ),
    ExpandedData = 
        Table.ExpandTableColumn(
            MergedData, 
            "CustomerInfo", 
            {"CustomerName", "Region"}
        )
in
    ExpandedData

5.2.2. データクレンジング時のコンテキスト利用

ファイルごとに異なるクレンジング処理が必要な場合、ファイル名に基づいて処理を変更します。たとえば、地域ごとに異なる形式のデータを統合する場合、地域別の処理を追加できます。

以下は、エクセルブック名が「America」だけ、日付を1日プラスする処理を行っています。

let
    Source = Folder.Contents("C:\Data\SalesData"),
    // 不要な列を削除
    RemovedOtherColumns1 = 
        Table.SelectColumns(
            Source,
            {"Content", "Name"}
        ),
    // ファイル名の拡張子を削除
    ExtractedTextBeforeDelimiter = 
        Table.TransformColumns(
            RemovedOtherColumns1, 
            {
                {
                    "Name", 
                    each Text.BeforeDelimiter(_, "."), 
                    type text
                }
            }
        ),
    // エクセルブックを読み込む
    AddedCustom = 
        Table.AddColumn(
            ExtractedTextBeforeDelimiter, 
            "Data", 
            each Excel.Workbook([Content], true)
        ),
    // エクセルシートを展開
    ExpandedWorkbook = 
        Table.ExpandTableColumn(
            AddedCustom, 
            "Data", 
            {"Name", "Data"}, 
            {"SheetName", "Data"}
        ),
    // 不要な列を削除
    RemovedOtherColumns2 = 
        Table.SelectColumns(
            ExpandedWorkbook,
            {"Name", "Data"}
        ),
    // エクセルシートのデータを展開
    ExpandedSheets = 
        Table.ExpandTableColumn(
            RemovedOtherColumns2, 
            "Data", 
            {"Date", "Sales"}, 
            {"Date", "Sales"}
        ),
    // エクセルブック名に基づいて処理を行う
    DateChange = 
        Table.AddColumn(
            ExpandedSheets,
            "NewDate",
            each 
                // エクセルブック名がAmericaなら、日付を＋１
                if [Name] = "America"
                then Date.AddDays([Date], 1)
                else [Date]
        )
in
    DateChange

以下は、エクセルブック名が「アメリカ」だけ「Region」列がないので、追加した後マージしています。

let
    Source = Folder.Contents("C:\Data\SalesData"),
    // 不要な列を削除
    RemovedOtherColumns1 = 
        Table.SelectColumns(
            Source,
            {"Content", "Name"}
        ),
    // ファイル名の拡張子を削除
    ExtractedTextBeforeDelimiter = 
        Table.TransformColumns(
            RemovedOtherColumns1, 
            {
                {
                    "Name", 
                    each Text.BeforeDelimiter(_, "."), 
                    type text
                }
            }
        ),
    // エクセルブックを読み込む
    AddedCustom = 
        Table.AddColumn(
            ExtractedTextBeforeDelimiter, 
            "Data", 
            each Excel.Workbook([Content], true)
        ),
    // エクセルシートを展開
    ExpandedWorkbook = 
        Table.ExpandTableColumn(
            AddedCustom, 
            "Data", 
            {"Name", "Data"}, 
            {"SheetName", "Data"}
        ),
    // 不要な列を削除
    RemovedOtherColumns2 = 
        Table.SelectColumns(
            ExpandedWorkbook,
            {"Name", "Data"}
        ),
    // エクセルブック名「America」だけ「Region」列を追加
    AddRegion = 
        Table.AddColumn(
            RemovedOtherColumns2,
            "AddRegion",
            each
                if [Name] = "America"
                then
                    Table.AddColumn([Data], "Region", each "America")
                else
                    [Data]
        ),
    // エクセルシートのデータを展開
    ExpandedSheets = 
        Table.ExpandTableColumn(
            AddRegion, 
            "AddRegion", 
            {"Region", "Date", "Sales"}, 
            {"Region"," Date", "Sales"}
        )
in
    ExpandedSheets