Forum Discussion
other
Dear all, is there a way to keep the alphabet order in a card + put other at the end?
kindly note that my data have a monthly update and I cannot do it manually since I have more than 80,000 raws.
ex:
Apple
Banana
Orange
Other
thank you in advance
Hi NourJ,
I'd like to suggest you add a custom column to check text column if it contains non-alphabet characters and replace them as 'other'. Then you can drag this column to multi-row card to achieve your requirement.
Result
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElVitWJVnJKzANCMNO/KDEvPRXKNALT5akpxmBGTKmBgZGZgyKYU5ySmAJkxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Replace", each if Text.Length(Text.Remove(Text.Lower([Text]),List.Transform({97..122 }, each Character.FromNumber(_))))>0 then "Other" else [Text]) in #"Added Custom"Regards,
Xiaoxin Sheng
1 Reply
Hi NourJ,
I'd like to suggest you add a custom column to check text column if it contains non-alphabet characters and replace them as 'other'. Then you can drag this column to multi-row card to achieve your requirement.
Result
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElVitWJVnJKzANCMNO/KDEvPRXKNALT5akpxmBGTKmBgZGZgyKYU5ySmAJkxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Replace", each if Text.Length(Text.Remove(Text.Lower([Text]),List.Transform({97..122 }, each Character.FromNumber(_))))>0 then "Other" else [Text]) in #"Added Custom"Regards,
Xiaoxin Sheng