Sort by Column does not work

[cy2452]

Hi ,

I am trying to sort the data by month in my visualization, and it is sorting alphabetically. So I created a column in my data table call sort and try to use the Sort by Column to sort it correctly, but it does not work. When I right click the clomn title and sort ascending, it works, but it will not help sort my visual.

My querys are linked by Post Month and Year Column

Please help. 

Thanks,

Chen

[v-jianboli-msft]

Hi @cy2452 ,

Here are two way to sort it:

1. Create SORTING column in power query, then you can use sort by column:

Add custom column and change data type:

Then add SORTING column:

Change the data type and remove custom column:

Here is the M code:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoUjAyUorViVbyTy5RMDIEM/3yy2BMx9J0GNM3Ea7WqzQPxnRLTYKLJsJFfRMrUdQaQpk5MKZLajKMGZxaAGbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [POST_MONTH_AND_YEAR = _t]),

    #"Added Custom" = Table.AddColumn(Source, "Custom", each "1 "&[POST_MONTH_AND_YEAR]),

    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}}),

    #"Added Custom1" = Table.AddColumn(#"Changed Type", "SORTING", each Date.Year([Custom])*100+Date.Month([Custom])),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"SORTING", Int64.Type}})

in

#"Changed Type1"

Now you can use sort by columns:

Final output:

2. Generate  SORTING column using DAX,

Here is the DAX:

SORTING =

var _a = CONVERT("1 "&[POST_MONTH_AND_YEAR],DATETIME)

return YEAR(_a)*100+MONTH(_a)

Apply it in the tooltips:

Note: If you want to sort in table visual, you need to add SORTING in the values field

Output:

Best Regards,

Jianbo Li

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

[cy2452]

No, that is a text field. I used Concat function in sql

MAX(CONCAT(LEFT(DATENAME(MONTH,ARPB_TRANSACTIONS_PAYMENT.POST_DATE),3),' ',(RIGHT(YEAR(ARPB_TRANSACTIONS_PAYMENT.POST_DATE),2)))) AS 'POST_MONTH_AND_YEAR'

[James-Harpin]

@cy2452 If you change it to a date field it should sort properly, you might have to use a Format function in your DAX

[James-Harpin]

Hi @cy2452 

What is the data type used for your Post_Month_And_Year? is it a date field