Create a table with unique list

[dearears]

Hi Forum,

I have this table

Mat Desc	Material Number	SubMat1	SubMat2	SubMat3	SubMat4	SubMat5
xxx	103842	39006582	100362311	43805638	43090505	null
xxx	103842	39006582	43758050	100362311	39005639	null
xxx	103842	44058607	null	null	null	null
xxx	103842	39006582	43758050	100362310	39005637	null
xxx	103842	39006582	100362311	39005638	null	null
xxx	103842	39006582	43676069	null	null	null
xxx	103842	39006582	100362311	43805638	41162738	null
xxx	103842	39006582	100362311	43676069	null	null
xxx	103842	39006582	43758050	100362310	40715734	null
xxx	103842	39006582	100362311	43805638	40221880	null
xxx	103842	39006582	43758050	100362310	39006660	39006921

from which i would like to create another table

Mat Desc	Material Number	Sub Components
xxx	103842	All unique subcomponents from columns SubMat1 - SubMat5
		..
		..

Could some one help me with this.

Thanks in advance.

[mahoneypat · Accepted solution]

You should unpivot your data to make this easier. In the query editor, select the first two columns, right click on them, and choose unpivot other columns. Once you load that table, you can use a measure with CONCATENATEX() to combine the values for each material in a table visual.

Pat

[dearears]

Thanks Pat ! I am nearly there.

The table I see now is

Mat Desc	Mat Number	Attribute	Value
xxx	123	Sub Name	yyyyy
xxx	123	Sub Number	3456
xxx	123	Sub Type	Type 1

How do it convert this to.

Mat Desc	Mat Number	Sub Name	Sub Number	Sub Type

TIA.