Sort by Column does not work

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.