Create a table with unique list

Question

Hi Forum,&nbsp;I have this tableMat DescMaterial NumberSubMat1SubMat2SubMat3SubMat4SubMat5xxx103842390065821003623114380563843090505nullxxx103842390065824375805010036231139005639nullxxx10384244058607nullnullnullnullxxx103842390065824375805010036231039005637nullxxx1038423900658210036231139005638nullnullxxx1038423900658243676069nullnullnullxxx103842390065821003623114380563841162738nullxxx1038423900658210036231143676069nullnullxxx103842390065824375805010036231040715734nullxxx103842390065821003623114380563840221880nullxxx10384239006582437580501003623103900666039006921&nbsp;from which i would like to create another table&nbsp;Mat DescMaterial NumberSub Componentsxxx&nbsp;103842All unique subcomponents from columns SubMat1 - SubMat5&nbsp;&nbsp;..&nbsp;&nbsp;..&nbsp;Could some one help me with this.&nbsp;Thanks in advance.

mahoneypat · Accepted Answer

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.
&nbsp;
Pat
&nbsp;

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

Mat Desc	Mat Number	Sub Name	Sub Number	Sub Type

Forum Discussion

Create a table with unique list

2 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Creating unique table without duplicate values

Create 3 digit sequential unique key

Create unique ID

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

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