Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
prassingh
Regular Visitor

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

 

 

1 ACCEPTED SOLUTION
_AAndrade
Super User
Super User

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:

_AAndrade_1-1709121054904.png

"
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:

_AAndrade_3-1709121110156.png

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:

_AAndrade_4-1709121238966.png


Final result:

_AAndrade_5-1709121258237.png

 










Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

2 REPLIES 2
prassingh
Regular Visitor

Thank you for the help, it worked.

_AAndrade
Super User
Super User

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:

_AAndrade_1-1709121054904.png

"
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:

_AAndrade_3-1709121110156.png

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:

_AAndrade_4-1709121238966.png


Final result:

_AAndrade_5-1709121258237.png

 










Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.