Forum Discussion

prassingh's avatar
prassingh
Regular Visitor
2 years ago
Solved

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 ...
  • _AAndrade's avatar
    2 years ago

    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: