How to sort alphaneumeric data in power BI

[prassingh]

Hi,

I want to sort the table data based on field, however the field contain alphaneumeric values as per below screen shots.

Current Result:

WD value will be static/it will not change. where same number can have multiple series (a,b,c,d,e).

Current_Result

WD-2C

WD-2B

WD-1B

WD-1A

WD-12B

WD-10A

WD-1

Expected Result

Exp_Result

WD-1

WD-1A

WD-1B

WD-2B

WD-2C

WD-10A

WD-12B

[prassingh]

Thank you for the help, it worked.

[_AAndrade · Accepted solution]

Hi,

I will solve the problem adding a new column to can order the current column. For doing this I create this funtion on Power Query:

"
let
ExtrairNumeroValor = (texto as text) =>
let
num = Text.Select(texto, {"0".."9"}),
alfa = Text.Select(texto, {"A".."Z"}),
numValue = if Text.Length(num) > 0 then Number.FromText(num) else 0,
alfaValue =
if Text.Length(alfa) > 0 then
List.PositionOf({"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}, Text.Upper(alfa)) + 1
else
0
in
numValue * 100 + alfaValue
in
ExtrairNumeroValor
"

And after use them to add a new colum using this script:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnfRNXJWitWBsJxgLEMEyxHOQpI2QIgqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstColumn = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"FirstColumn", type text}}),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([FirstColumn], "-"), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Text After Delimiter",{{"Text After Delimiter", "Split"}}),
AddColumn = Table.AddColumn(#"Renamed Columns", "Valor", each ExtractValue([Split]),Int64.Type)
in
AddColumn

After order the Column by this new column using this path:

Final result: