Pivoting/unpivoting/grouping help needed

[jthomson]

Hi,

I've got a bunch of data where the main ID of each case is of the format 123456X, where 123456 is the main case number, and X is an optional ending to indicate a case type. I'm needing to group everything by the main case number, but retain the information in all other columns, making a duplicate column for each possible case type with the case type within the column name (kind of how it works when you expand a table after a merge of queries) - so something like this, very simplified, once I've separated out the main ID field into case number and case type:

I could do this by duplicating the queries for each possible case type, filtering on the case type column and then merging everything back together, but I'm thinking there's got to be a more efficient way to do this which doesn't require duplication (there's about 8-9 different case types and the potential for more to be added later) and have lots and lots of intermediate queries. I'm sure I've seen something similar on the forums before but for the life of me I can't find it. Any ideas on how to get this done?

[v-piga-msft · Accepted solution]

Hi  @jthomson,

What visual do you want to show the data? Table or Matrix? Matrix  should be easier.

1.Go to Query Qditor and select columns data1 and data2 then click Unpivot Column.

2. Then combine the column data and type by adding a custom column.

3. You could remove the columns type and Attribute, apply and Close.

4.Create the matrix with columns case, custom and values.

 

If you want to show as table, you may add one more step after the step 2 above in Query Editor.

You need pivot the column custom.

Then you will get the new table like below.

Hope this can help you!

Best Regards,

Cherry

[jthomson]

Think either I've not been clear or you've misunderstood what I'm after - I need the data model to look like the end result, I'm not going to show that output in a visual, but with some tweaks to what you've suggested with the pivoting step I think I've got it to work, cheers

[v-piga-msft]

Hi  @jthomson,

 I need the data model to look like the end result, I'm not going to show that output in a visual, but with some tweaks to what you've suggested with the pivoting step I think I've got it to work, cheers

---

For your this requirement, you could refer to the second way I have replied first time. 

After the transform in Query Editor, you could get the data model look like the end result.

It seems that you have found the solution, only thing that you'll have to notice, just always share your solution or accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry