sort data type

[abgnfirdaus]

Hello there, I need help. Based on the table below.

Equipment	Type	XLPE Outcome	PILC Outcome
A	XLPE	BAD	-
B	XLPE	GOOD	-
C	PILC	-	BAD
D	PILC	-	BAD

I would like to make a table visualization show the equipment and the equipment type outcome automatically without showing unrelated type. The TABLE VISUALIZATION should show like this:

Equipment	Type	Outcome
A	XLPE	BAD
B	XLPE	GOOD
C	PILC	BAD
D	PILC	BAD

Hope you guys can help me. thank you so much!

[Anonymous]

Hi,

you can use the conditional column option in the power query editor to solve this. 

[Tahreem24]

@abgnfirdaus Attaching screen shot for your reference:

[Tahreem24]

@abgnfirdaus Create a column like below:

Column = IF(MyTable[Type]="XLPE",MyTable[XLPE Outcome],MyTable[PILC Outcome])

[SteveHailey · Accepted solution]

Hi @abgnfirdaus. There are a number of ways to do this, but I would probably do it in Power Query.

Hold the control key and click to selected the XLPE Outcome column and then with control still held click PILC Outcome column to select it also. Then Right Click and choose Replace Values, and in Value to Find put a "-", and in Replace With, leave it blank. This will get rid of the dashes.

Then with the two columns still selected, click the Merge Columns button on the Transform tab of the ribbon. Enter "Outcome" for the name of the new column and leave the Seperator as --None--.

Your data will now look as it is below, and you can Close and Apply your changes and create your table visual.

Alternatively, you could create a Conditional Column as below:

which is the same as creating a Custom Column with this code:

if [Type] = "XLPE" then [XLPE Outcome] else if [Type] = "PILC" then [PILC Outcome] else ""

I created a .pbix file that is linked here, in case it's helpful.

-Steve