Transform Table of Multiple Columns to Single Column

Question

I am new to DAX and need some help figuring out the proper formula and syntax for a table transformation, which I can hopefully explain in a way that makes sense...

I have Table X with Unique IDs and several True/False columns, and in order to get the visualizations in the way I want, need to be able to transform the table so that instead there is a new combined column where every True value from the original table is instead populates the name of that original column name.

So the original table is formatted like this:

ID	A	B	C
1	TRUE	FALSE	TRUE
2	FALSE	FALSE	TRUE
3	TRUE	FALSE	FALSE
4	TRUE	TRUE	FALSE
5	TRUE	TRUE	FALSE
6	FALSE	FALSE	TRUE
7	FALSE	TRUE	TRUE

and would like to be able to create a new table so that it instead looks like this:

ID	COMBINED
1	A
1	C
2	C
3	A
4	A
4	B
5	A
5	B
6	C
7	B
7	C

I've played around with unpivoting the columns and then trying to create a formula based on the new unpivoted columns, but I haven't been able to find the right functions to make this happen, but can't help but think it's entirely possible and probably not even especially complicated...

any help is appreciated, thanks!

v-lili6-msft · Accepted Answer

HI&nbsp;&nbsp;bmcirillo&nbsp;
There are two way to for you refer to:
1. Duplicate a table in edit queries, then unpivot the Column A,B,C , filter out false and remove this column.

&nbsp;
2. Use Union to create a new calculate table&nbsp;
New table = 
UNION(SELECTCOLUMNS(FILTER('Table','Table'[A]=TRUE()),"ID",[ID],"COMBINED","A"),
      SELECTCOLUMNS(FILTER('Table','Table'[B]=TRUE()),"ID",[ID],"COMBINED","B"),
      SELECTCOLUMNS(FILTER('Table','Table'[C]=TRUE()),"ID",[ID],"COMBINED","C")
     )
Result:

&nbsp;
and here is sample pbix file,please try it.
&nbsp;
Regards,
Lin

Forum Discussion

Transform Table of Multiple Columns to Single Column

2 Replies

Related content

Transform columns / type

transform multiple columns into 2 columns (List.Zip, (un)pivot)

Table Transformation

Transforming multiple columns

Transform Column / Type