One column with different questions in the same table

[Anonymous]

I have one table with this data

Assignment group

Metric

Value

String value

ticket number

Value is the question we make to the employees like:

what did you like most about our service

We are sorry that you did not have a service as you expected 

and 3 more like this

The string value is the comment to the value data like

value                                           string value

we are sorry bla bla bla              your service is not good enough and i need more sweets in my tab

I would like to achieve something like this

Ticket number             assignment group            value  1                                string value 1                      value 2       string value 2

inc0000123                  IT FRance                          we are sorry bla bla           my table is full of dust        what bla bla   i like all

but unfortunatelly what i have achieved is this

Ticket number             assignment group            value                                  string value                      

inc0000123                  IT FRance                          we are sorry bla bla           my table is full of dust    

inc0000123                  IT FRance                           what bla bla                      i like all

Any idea how can I achieve this?, a matrix visual is not the solution as I don´t want to expand the ticket number to see the assignment group etc

Thanks!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

If you'd like this output,

please check my method below:

In Edit queries,

1. sort rows on Ascending-> first sort by "Ticket number", second by "assignment group",

    then add an index column from 1

2. select "value" and "string value" columns, then select "unpivot columns",

3. add conditional column

4. merge columns

5. Close&&apply

Add columns into a matrix,

select the icon on the top-right of the matrix which means :expand all down one level

In Format setting, turn off Row header->Stepped layout,

turn off row/column subtotal,

To make the columns show as the order of your example,

please select the column "Merged" which is added into "Column" field of the matrix,

then select "Modeling"->Sort by column->select "rank_2" column,

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sturlaws]

Hi, @Anonymous, 

it's doable, but it involves some Power Query massaging. You will have to create some nested indexes:  https://www.myonlinetraininghub.com/numbering-grouped-data-power-query

Since you have not provided any sample data, I have created a very simple mockup dataset to show how it can be done:

mockup report

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.