Is there anyway to extract column name for the value equals 'X' in power BI?

Question

My first ever question on this forum. I am relatively new to Power BI and would really appreciate support from the experts in the community.

I am trying to extract the column names for each values of 'X'. I need to look at columns B to J. Is there a way to do this in power BI.

Appreciate any help. I have attached a screenshot to help understand, let me know if you need more information.

Many Thanks Azhar

Sample data

Answer 1

Currently your data looks like this:

ES No.	ENCL	TYPE	ELEC
0001			X
0002			X
0003	X

For the sake of demonstration I've simplified it a bit.

From this, you want to unpivot your columns. It's performed in PowerQuery :

1. Go in PowerQuery (Transform Data) in the ribbon.
2. In your table, selection columns B to J.
3. In the ribbon, Transform tab, choose Unpivot Columns.

Your new table looks like this :

ES No.	Attribute	Value
0001	ENCL
0001	ELEC	X
...	...	...
0002	ELEC	X

Then, you can create a measure to return the Attribute value for each ES No. where the value is an X. We'll assume there is only one X per ES No.

XCol =
CALCULATE(
    MAX(Table[Attribute]),
    Table[Value] = "X"
)

Note, you can avoid getting a value in the Total lines by tweaking the measure.