Multiple Sort Columns, Token Comma Error

[PPalkowski]

In power Query I was trying to sort by using two columns and I keep getting this error.

Expression.SyntaxError: Toekn Comma expected.

= let
Source = Excel.Workbook(File.Contents("M:\Quarterly KPI\KPI Data.xlsx"), null, true),
ALLDATA_Sheet = Source{[Item="ALLDATA",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(ALLDATA_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"QRT", type text}, {"MONTH", type text}, {"CAT", type text}, {"TYPE", type text}, {"VALUE", Int64.Type}}),
#"Sorted Rows" = Table.Sort({{"MONTH", Order.Ascending}},{"CatSort",Order.Ascending}})
in
#"Changed Type"

[dax · Accepted solution]

Hi PPalkowski,

Whehn you want to sort, you could refer to refer to below M code(which refer to previous step's result and sort acs)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTICYQNDS31DfUOlWJ1opSSggDFM0AhJ0AxdJUi7OYrKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, amount = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"amount", Int64.Type}, {"date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"date", Order.Ascending}, {"amount", Order.Ascending}})
in
    #"Sorted Rows"

You need remove }   from MONTH, and add a reference in sort function.

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @PPalkowski

I think you have an extra closing } after the first sorting condition in
Table.Sort({{"MONTH", Order.Ascending}} /* <- */,{"CatSort",Order.Ascending}})

Kind regards
JB