Table Array

[dmcmillin]

Hi All,

I have tried using pivot and unpivot and transposing but can't quite figure out how to transform my table from this:

Any help would be appreciated

Part Num	To Part
CCXCV	C7XTN
CCXCV	P5YFN
CCXCV	TNTCT

to this:

Part Num	Dev1	Dev2	Dev3
CCXCV	C7XTN	P5YFN	TNTCT

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new table by using DAX.

New Table = 
ADDCOLUMNS (
    VALUES ( Data[Part Num] ),
    "Dev1",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX ( 1, SUMMARIZE ( Data, Data[To Part] ), ORDERBY ( Data[To Part] ) )
        ),
    "Dev2",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX ( 2, SUMMARIZE ( Data, Data[To Part] ), ORDERBY ( Data[To Part] ) )
        ),
    "Dev3",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX ( 3, SUMMARIZE ( Data, Data[To Part] ), ORDERBY ( Data[To Part] ) )
        )
)

[dmcmillin]

@Jihwan_Kim you understood the concept. Thank you.

This is very odd, and may be because I am a new user but I can't reply with a .pbix file like you did.

I am unable to get what you produced to work and might be because I provided partial data. Here is a larger sample size which has different parts and different deviations that would have to be transposed:

Part Num	To Part
CCR41	JW30H
CCXCV	C7XTN
CCXCV	P5YFN
CCXCV	TNTCT
CC0F0	G6R99
CC0F0	56D99
CDDMJ	XX7MT
CF4C1	CC0F0
CGNVH	6KNWW
CG0M0	K18NP
CG48D	0W8VV
CJMMK	6FDH8
CJX0F	WV4KK
CMFND	6M9N4
CMPGM	Y26KX
CM9TJ	X3PK1
CPFWH	WCF6J
CPFWH	2N6C5
CP610	6JKXY
CR72X	68ND3
CTWCN	C8T2P
CTWCN	MGPPC

to look like this:

Part Num	Dev1	Dev2	Dev3
CCR41	JW30H
CCXCV	C7XTN	P5YFN	TNTCT
CC0F0	G6R99	56D99
CDDMJ	XX7MT
CF4C1	CC0F0
CGNVH	6KNWW
CG0M0	K18NP
CG48D	0W8VV
CJMMK	6FDH8
CJX0F	WV4KK
CMFND	6M9N4
CMPGM	Y26KX
CM9TJ	X3PK1
CPFWH	WCF6J	2N6C5
CP610	6JKXY
CR72X	68ND3
CTWCN	C8T2P	MGPPC

[Jihwan_Kim · Accepted solution]

Hi, please check the below picture and the attached pbix file.

I am not sure how it is defined to understand what comes for Dev1 / Dev2 / Dev3, but I hope you get the ideas on how to create the solution for your business logic.

New Table =
ADDCOLUMNS (
    VALUES ( Data[Part Num] ),
    "Dev1",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                1,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        ),
    "Dev2",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                2,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        ),
    "Dev3",
        CALCULATE (
            MAX ( Data[To Part] ),
            INDEX (
                3,
                SUMMARIZE (
                    FILTER ( Data, Data[Part Num] = EARLIER ( Data[Part Num] ) ),
                    Data[To Part]
                ),
                ORDERBY ( Data[To Part] )
            )
        )
)

[dmcmillin]

@Jihwan_Kim Thank you! I will make this work.