PowerApps コレクションの変換2

# 元データ

Parent_table
|id |product_name|
|---|---|
|1  |商品A|
|2  |商品B|

Child_table
|id |product_id |year|month |sales |
|---|---|---|---|---|
|1   |1|2024  |1  |100|
|2   |1           |2024 |2  |200
|3   |1           |2024 |3  |300
|4   |1           |2024 |4  |400
|5   |1           |2024 |5  |500
|6   |1           |2024 |6  |600
|7   |1           |2024 |7  |700
|8   |1           |2024 |8  |800
|9   |1           |2024 |9  |900
|10   |1           |2024 |10  |1000
|11   |1           |2024 |11 |1100
|12  |1           |2024 |12 |1200
|13   |1|2025  |1  |100|
|14  |1           |2025 |2  |200
|15  |1           |2025 |3  |300
|16  |1           |2025 |4  |400
|17  |1           |2025 |5  |500
|18  |1           |2025 |6  |600
|19  |1           |2025 |7  |700
|20  |1           |2025 |8  |800
|21  |1           |2025 |9  |900
|22   |1           |2025 |10  |1000
|23   |1           |2025 |11 |1100
|24  |1           |2025 |12 |1200

# 目標
target_col
|id |product_name|month_col
|---|---|---
|1  |商品A|month_col

month_col
|year |month_1 |month_2|month_3|month_4 |month_5|month_6|month_7 |month_8|month_9|month_10 |month_11|month_12|id_1 |id_2|id_3|id_4 |id_5|id_6|id_7 |id_8|id_9|id_10 |id_11|id_12
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---
|2024|100  |200|300|400  |500|600|700  |800|900|1000  |1100|1200|1  |2|3|4  |5|6|7  |8|9|10  |11|12
|2025|100  |200|300|400  |500|600|700  |800|900|1000  |1100|1200|13  |14|15|16  |17|18|19  |20|21|22  |23|24

# 方法
```
//Child_tableをグループ化にする
ClearCollect(
    Parent_table_group_col,
        AddColumns(
            Parent_table As a,
            group,
            ShowColumns(
                Filter(
                    Child_table As b,
                    a.id = b.product_id
                ),
                id As sale_id,
                year,
                month,
                sales
            )
        )
);


ClearCollect(
    year_col,
    [
        Year(Now()),
        Year(Now()) + 1
    ]
);

ClearCollect(
        target_col,
        DropColumns(
            AddColumns(
                Parent_table_group_col,
                month_col,
                ForAll(
                    year_col,
                    {
                        year: Value,
                        month_1: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 1
                            )
                        ).sales,
                        month_2: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 2
                            )
                        ).target_value,
                        month_3: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 3
                            )
                        ).target_value,
                        month_4: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 4
                            )
                        ).target_value,
                        month_5: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 5
                            )
                        ).target_value,
                        month_6: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 6
                            )
                        ).target_value,
                        month_7: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 7
                            )
                        ).target_value,
                        month_8: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 8
                            )
                        ).target_value,
                        month_9: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 9
                            )
                        ).target_value,
                        month_10: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 10
                            )
                        ).target_value,
                        month_11: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 11
                            )
                        ).target_value,
                        month_12: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 12
                            )
                        ).target_value,
                        id_1: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 1
                            )
                        ).sale_id,
                        id_2: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 2
                            )
                        ).sale_id,
                        id_3: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 3
                            )
                        ).sale_id,
                        id_4: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 4
                            )
                        ).sale_id,
                        id_5: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 5
                            )
                        ).sale_id,
                        id_6: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 6
                            )
                        ).sale_id,
                        id_7: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 7
                            )
                        ).sale_id,
                        id_8: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 8
                            )
                        ).sale_id,
                        id_9: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 9
                            )
                        ).sale_id,
                        id_10: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 10
                            )
                        ).sale_id,
                        id_11: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 11
                            )
                        ).sale_id,
                        id_12: First(
                            Filter(
                                group As b,
                                b.year = Value,
                                b.month = 12
                            )
                        ).sale_id
                    }
                )
            ),
            group
        )
    );
 ```
# 子供のmonth_colを更新時
```
ClearCollect(month_col,First(Filter(target_col,id=1)).month_col);
Patch(month_col, {year:2024}, {month_1:999});
Patch(target_col, {id:1}, {month_col:month_col});

```