Unpivot columns in Power Query

Hi  Anonymous ,

Here are the steps you can follow：

1. Transform – UnpivotColumns -- Yellow-marked columns .

2. In Power query. Add Column – Index Column – From 1.

Result:

3. Create calculated table.

Flag1 =
SUMMARIZE('True','True'[Region],'True'[SubRegion],'True'[Location],'True'[Key],'True'[Node],'True'[Sequence_Category],'True'[Score],'True'[Items_Type],'True'[Index])

Table 3 =
VAR _table1 =
    FILTER ( 'Flag1', [Group] <> BLANK () )
RETURN
    SUMMARIZE (
        _table1,
        [Region],
        [SubRegion],
        [Location],
        [Key],
        [Node],
        [Sequence_Category],
        [Score],
        [Items_Type],
        [Group],
        "Device_Name", [Group],
        "Value1",
            MAXX (
                FILTER (
                    ALL ( 'True' ),
                    OR (
                        'True'[Attribute] = "GroupA_Device_Name",
                        'True'[Attribute] = "GroupB_Device_Name"
                    )
                        && LEFT ( 'True'[Attribute], 6 ) = [Group]
                        && 'True'[Key] = EARLIER ( 'Flag1'[Key] )
                ),
                [Value]
            ),
        "Device_Category", [Group],
        "Value2",
            MAXX (
                FILTER (
                    ALL ( 'True' ),
                    OR (
                        'True'[Attribute] = "GroupA_Device_Category",
                        'True'[Attribute] = "GroupA_Device_Category"
                    )
                        && LEFT ( 'True'[Attribute], 6 ) = [Group]
                        && 'True'[Key] = EARLIER ( 'Flag1'[Key] )
                ),
                [Value]
            ),
        "Device_Number", [Group],
        "Value3",
            MAXX (
                FILTER (
                    ALL ( 'True' ),
                    OR (
                        'True'[Attribute] = "GroupA_Device_Number",
                        'True'[Attribute] = "GroupA_Device_Number"
                    )
                        && LEFT ( 'True'[Attribute], 6 ) = [Group]
                        && 'True'[Key] = EARLIER ( 'Flag1'[Key] )
                ),
                [Value]
            ),
        "Device_ID", [Group],
        "Value4",
            MAXX (
                FILTER (
                    ALL ( 'True' ),
                    OR (
                        'True'[Attribute] = "GroupA_Device_ID",
                        'True'[Attribute] = "GroupA_Device_ID"
                    )
                        && LEFT ( 'True'[Attribute], 6 ) = [Group]
                        && 'True'[Key] = EARLIER ( 'Flag1'[Key] )
                ),
                [Value]
            )
    )

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly