How to sort the column?

[Anonymous]

Hi!

I have a column, text type as follows:

before 200001

200001

200002

200003

200004

......

202210

Please help me how to sort them so that before 200001 is on the first place. Thank you!

[mangaus1111 · Accepted solution]

Hi @Anonymous ,

insert in your data model a sort table like this and make a relatiosnship between the 2 columns

Don´t forget to sort the Column Text in the Sort Table by the Index Column.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! But it always says a circular dependancy was detected. Before 20000101, 20000101.... is a calculated column not imported from.

[mangaus1111]

You need a data model with 2 tables realted 1 to many: Sort Table as Dimension and Fact Table.

You need to import the Sort Table

[Anonymous]

Because this text column is not imported but a calculated column, I can not insert an index column using power query.  What I did is new column=if (text="before 20000101", "20000100", text), and then I use this column to sort the text column. However when I sort it, it can not work and shows "circular dependancy."

[mangaus1111 · Accepted solution]

Hi @Anonymous ,

see my pbi file here. I hope this helps

https://1drv.ms/u/s!Aj45jbu0mDVJiHfRIaja1FReNxxK?e=C08XII

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@mangaus1111 Thank you! May I ask you how to insert index and how to make "before 20000101" as index 1? Also the last number is "Now"

before 200001

200001

200002

200003

200004

......

202210
Now

[mangaus1111]

you can easily import in power query  table like this

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111]

Use the column SortTable[Text] in the row of your visual, otherwise it does not work.

[mangaus1111 · Accepted solution]

Hi @Anonymous ,

insert in your data model a sort table like this and make a relatiosnship between the 2 columns

Don´t forget to sort the Column Text in the Sort Table by the Index Column.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.