Retrieving the Column Header that Corresponds with a Matched Value

[DanielRatvai]

Hello, 

I need to create a Quality Check reports, which is showing different error messages related to incidents. It should look like the following: (behind every QAx column, we will have different calculations, which will give back a true or false value, like: if the created date is higher than the closed date, then the QA1 should true.)

TICKET_ID	QA1	QA2	QA3	QA4	QA5	QA6
1	TRUE				TRUE
2		TRUE
3			TRUE
4				TRUE
5					TRUE
6
7						TRUE

I would like to create a new column (ERROR_CODE) which will list every QA code where the ticket has TRUE value.

TICKET_ID	QA1	QA2	QA3	QA4	QA5	QA6	ERROR	ERROR_CODE
1	TRUE				TRUE		TRUE	QA1,QA5
2		TRUE					TRUE	QA2
3			TRUE				TRUE	QA3
4				TRUE			TRUE	QA4
5					TRUE		TRUE	QA5
6							FALSE
7						TRUE	TRUE	QA7

Can somebody help me out with this?

Thanks and regards,
Daniel

[v-stephen-msft · Accepted solution]

Hi @DanielRatvai ,

Agree with @lbendlin . In Power Query, you can use unpivot columns and Text.Combine() to achieve it.

Reference:

Unpivot columns - Power Query | Microsoft Learn

excel - PowerQuery/M: How can I combine text from multiple rows into one row - Stack Overflow

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[lbendlin]

There is no magic to it - DAX does not have a COLUMNNAMES  function (unlike Power Query).  You need to hardwire the logic.