Trying to sort column headers

mp390988 
Instead of creating a calculated column in the reporting view. Create custom column on the power query layer (Transform Data layer) please refer the below M expression and add it as the last step of your transformation

SortedColumn = = Table.AddColumn(PreviousStep, "HeaderSort", each if [ColumnType] = "Revenue" then 0 else 1)

Use this custom column (HeaderSort) to sort the ColumnType Column, now you will not get circular dependency issues.

Thanks,

Jai

I had the same issue as the OP with a column that determines column header values, e.g. 

1-Pear

2-Apple

3-Grapes

I really didn't want to include the 1-, 2-, 3- in the column text results for sorting, but the column calculations are really heavyweight so I didn't want to dupe the column just to create a sort order output I could use to sory the original.  Thought about some of the other approaches that happen to be mentioned in this thread, e.g. a separate sort table accessed through a join, but they are too clunky for my taste.

Went searching and happened to come across your suggestion in this thread.  It's brilliant, and works perfectly.    I was actually able to simplify it since I don't need a new colunn to do this.  I just added the zero-space character, repeated appropriately, to the text output of my original column.  e.g.

REPT(UNICHAR(8203), 3,) & "Pear"

REPT(UNICHAR(8203), 2) & "Apple"

REPT(UNICHAR(8203), 1) & "Grapes"

I can't mark this as a solution because this isn't my thread, but I wanted to tip my cap to you for the idea.  Hopefully someone else will come across this in the future and can use my post here as confirmation that this solution works well.  Thank you!