How to change How Table appears

[Eli_]

Hello all,

I would like to change how my table appears, not sure If I need to make this change by using a new visual or by doing some more transformation in the query editor.

Here's how it currently appears in the Table Visual:

Table 1:

No	Cat	Qt	Cost	Ldate	Col Aval
01	New	7	$30.00	01-05-2018	13
04	New	20	$48.25	01-02-2018	26
06	Old	10	$24.00	01-12-2018	23

I would to have the table above displayed as the  below instead;

Table 2:

01	No	01
01	Cat	New
01	Qt	7
01	Cost	$30.00
01	Ldate	01-05-2018
01	Col Aval	13

06	No	06
06	Cat	Old
06	Qt	10
06	Cost	$24.00
06	Ldate	01-12-2018
06	Col Aval	23

I was "somewhat" able to achieve the result in Table 2 by doing some more transformation in the query editor, and ended up having something like what you see in Table 3 below.

Table 3

No	Attribute	Value
04	No	04
04	Cat	New
04	Qt	20
04	Cost	$48.25
04	Ldate	01-02-2018
04	Col Aval	26

But then the challenge becomes field formatting, all data types(Test, Number, Date, Currency) ended up converting to Text, and did not appear good. This is because Value ended up becoming a single Columns with text data type(ignoring the Number, Date and Currency)

Can someone please help with a better way of getting this done?

[Eli_]

I have marked @jdbuchanan71 response as the solution to this problem, as his/her solution document directed me to exactly what I wanted.

For anyone looking for the solution to this problem, and does not want to download the solution file uploaded by jdbuchanan71.

There two main steps required to achieve the result in Table 2 from the Table 1.

Step 1:  Change the visual used from Table to Matrix

Setp 2: Click on Format > Values > enable Show on rows

[jdbuchanan71 · Accepted solution]

Hello @Eli_ 

Rather than unpivoting your data you can write a measure to calculate the value to display then add the No field as the column, put the measures if in the values and set the values to display on rows.  That way you can format each measure individually.  My measure names start with 'm_' just bacause you can't have a measure and a column named the same.

I have attached my sample file for you to take a look at.

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

[Eli_]

Hello @jdbuchanan71,

Thanks for helping on this the last time. Please there's still one more problem I'm facing.

When the "No" column is duplicate in the Table, it sums up in Matrix Visual.

See the No  1 has two records, I have set Summarization to Don't summarize. But I still get the Result below.

How do i prevent the summiraztion, and have 2 individual (No. 1) and not 1 Summarized one ???

See the Table

See the Matirix Visual result