Power Query でSample Data 生成して、それの中の抜き出しを Dax でやってみた

背景

ほんと、どうでもいい記録

会社でなんか相談が上がってたんで、久々に Power Query/BI を触ってみるかと思った記録

概要

• 製造データと不良品データを突き合わせて、製造日を出したい。のだと思われる。（よく知らない
• その際、製造データが大量にあり過ぎるので、out of memory になるとかなんとか

Sample Data 生成

欲しいイメージはこんな感じ

まずは、Power Query で動的生成

久々にやってみたけど、Serial Number の継続が意外に面倒だった。
SerialMap, DateMap として更新していったけど、もっと良い方法がありそうな気がして困る

power query での生成例

適当に生成してみる例

let
    // 基本設定: 生成行数
    rowCount = 100000,
    // 基本設定: マシーンタイプ
    machineTypes = {"Type1", "Type2", "Type3", "Type4", "Type5"},
    startDate = #date(2024, 4, 1),

    // 初期化
    initialState = [
        SerialMap = [Type1 = 1, Type2 = 1, Type3 = 1, Type4 = 1, Type5 = 1],
        DateMap = [Type1 = startDate, Type2 = startDate, Type3 = startDate, Type4 = startDate, Type5 = startDate],
        Index = 0,
        newRow = [
            beginSerial = null,
            endSerial = null,
            machineType = null,
            productDate = null
        ]
    ],

    // 各行を生成
    generateRows = List.Generate(
        () => initialState,
        each [Index] < rowCount,
        each [
            // ランダムに productNumber を生成
            productNumber = Number.RoundDown(Number.RandomBetween(1, 30)),

            // ランダムに machineType を選択
            machineType = machineTypes{Number.RoundDown(Number.RandomBetween(0, 5))},

            // 現在の serialNumber と日付を取得
            currentSerial = Record.Field([SerialMap], machineType),
            currentDate = Record.Field([DateMap], machineType),

            // SerialMap を更新
            SerialMap = Record.TransformFields([SerialMap], {{machineType, each _ + productNumber}}),

            // DateMap を更新
            DateMap = Record.TransformFields([DateMap], {{machineType, each _ + #duration(1, 0, 0, 0)}}),

            // インデックスを増加
            Index = [Index] + 1,

            // 新しい行を返す
            newRow = [
                beginSerial = currentSerial,
                endSerial = currentSerial + productNumber - 1,
                machineType = machineType,
                productDate = currentDate
            ]
        ],
        each [newRow]
    ),

    // List を Table に変換
    validRows = List.RemoveFirstN(generateRows, 1),
    resultTable = Table.FromRecords(validRows, {"beginSerial", "endSerial", "machineType", "productDate"}),

    // 型を変更
    changedTypes = Table.TransformColumnTypes(resultTable, {{"beginSerial", Int64.Type}, {"endSerial", Int64.Type}, {"machineType", type text}, {"productDate", type date}})
in
    changedTypes

念の為 C# 版も

Power Query でやるとなんか別の影響出ちゃうかもと重い、csv 生成用に用意してみた

C# での生成例

適当に生成してみる例

class Program
{
    static void Main()
    {
        string filename = GenerateFilename();
        SaveFileDialogAndOutputCsv(filename);
    }

    // ファイル名生成メソッド
    private static string GenerateFilename()
    {
        return DateTime.Now.ToString("yyyyMMdd_") + "machineData.csv";
    }

    // CSV出力メソッド
    private static void SaveFileDialogAndOutputCsv(string filename)
    {
        var saveFileDialog = new Microsoft.Win32.SaveFileDialog
        {
            Title = "保存先を指定してください",
            InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory),
            Filter = "CSVファイル(*.csv)|*.csv",
            FileName = filename
        };

        if (saveFileDialog.ShowDialog() == true)
        {
            using (var writer = new StreamWriter(saveFileDialog.FileName, false, Encoding.UTF8))
            {
                writer.WriteLine("machineType,beginSerial,endSerial,productDate");
                GenerateAndWriteData(writer);
            }
        }
    }

    // データ生成とCSV書き込みメソッド
    private static void GenerateAndWriteData(StreamWriter writer)
    {
        string[] machineTypes = { "Type1", "Type2", "Type3", "Type4", "Type5" };
        DateTime startDate = new DateTime(2024, 4, 1);
        Random random = new Random();

        foreach (var type in machineTypes)
        {
            int serial = 1;
            for (int i = 0; i < 500000; i++)
            {
                var productNumber = random.Next(1, 10);
                string productDate = startDate.AddDays(i).ToString("yyyy/MM/dd");
                writer.WriteLine($"{type},{serial},{serial + productNumber - 1},{productDate}");
                serial += productNumber;
            }
        }
    }
}

そうそう、不良データも

イメージはこんな感じ

こっちは、確認用に serial を変えたかったんで、Dax で用意。ええんかな？

不良データ

DefectTable_csv = 
VAR machineTypes = {"Type1", "Type2", "Type3", "Type4", "Type5"}
VAR productSerial = GENERATESERIES(100, 500, 1)
RETURN
GENERATE(
    SELECTCOLUMNS(
        machineTypes,
        "machineType", [Value]
    ),
    TOPN(1, 
        SELECTCOLUMNS(
            productSerial,
            "productSerial", [Value]
        ), RAND())
)

で、実際に、抽出を Dax で

苦手なまま、勉強出来ていない Dax に挑戦

読み込んだ不良データに、列追加版

列追加

defectDate = 
CALCULATE(
    FIRSTDATE('productLog'[productDate]),
    FILTER(
        'productLog',
        'productLog'[machineType] = EARLIER([machineType])
        && 'productLog'[beginSerial] <= EARLIER([productSerial])
        && 'productLog'[endSerial] >= EARLIER([productSerial])
    )
)

Table として作成版

テーブル追加版

ViewTable_csv = 
ADDCOLUMNS(
    DefectTable_csv,
    "productDate", 
    CALCULATE(
        MIN(productCsv[productDate]),
        FILTER(
            productCsv,
            productCsv[machineType] = DefectTable_csv[machineType]
            && productCsv[beginSerial] <= DefectTable_csv[productSerial]
            && productCsv[endSerial] >= DefectTable_csv[productSerial]
        )
    )
)

あとがき

そもそも、以下にしても状況再現っぽいことが出来なかったので、諦めた
諦めたけど、少しは努力したので、記録しておくのである

• power query で 100万行
• csv で、250万行