Forum Discussion
How to sort alphaneumeric data in power BI
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 |
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:
2 Replies
Thank you for the help, it worked.
_AAndradeResident Rockstar
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:
