Sorting columns of matrix on unpivot query

[TakaHom]

Hello everyone, I'm very beginner on BI. have a lot of trouble with it. Please help me!

I have a unpivoted query like following.

ID, Period, Number

1, 1, 3.4

1, 5, 2.4

1, 10, 5.3

1, Qtr, 5.5

1, YTD, 12

And the current matrix is following.

ID, 1, 10, 5, Qtr, YTD

1, 3.4, 5.3, 2.4, 5.5, 12

I’d like to create following. Please tell me how to do it!

ID, 1, 5, 10, Qtr, YTD

1, 3.4, 2.4, 5.3, 5.5, 12

OR

ID, Qtr, YTD, 1, 5, 10

1, 5.5, 12, 3.4, 2.4, 5.3

I imagine if I could set any dummy columns and hide them for ordering, I would be able to realize what I want to do….
Are there any good options?

[v-yiruan-msft · Accepted solution]

Hi @TakaHom ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a sort table just as suggested by @lbendlin 

2. Create a matrix visual (Rows: ID   Columns: Period (from Sort table)  Values: Number)

3. Select the field Period in Sort table and navigate to Column tools, Sort by column: Order

Best Regards

[lbendlin]

Create a separate table that has an index column and the order you want the periods to appear in

1 1

2 5

3 10

4 QTR

5 YTD

Link that table into the data model (or merge it in Power Query) and then use the index column to sort the period column ("Sort by other column")

[TakaHom]

Thank you for your advice, but I could not solve my trouble. I think I could trace your advice completely. I could sort it’s rows by the table. But I'm sorry, what I want to do is to sort the columns after pivot data table. If I could specify the order of the columns of pivot table, I think the problem would be solved.
Thank you in advance.