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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

split column to get only integers

Hello all,

 

Hope you all doing well. I need your help in my data query.  i have a column called ABC with data as follows:

 

Ravi-1

Syntel-42

Phoenix-34

Query-67
Loading...

Query-Manipulated

central

 

Now, i want to get a column with only numbers(present at the end). for values with no integer i can have 0. i tried with split column, but as data is not in proper format it doesn't help.

 

Also, Mquery i cant touch.

 

Please provide me with good solution.

 

Thankyou

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

I;d like to suggest you use Power Query. I created data to reproduce your scenario.

Table:

b1.png

 

You may add a new custom column with the following formula.

 

= Table.AddColumn(#"Changed Type","Result",each if Text.Length (Text.TrimStart([ABC],{"a".."z","A".."Z"}&{"-","."}))=0 then 0 else Number.FromText(Text.TrimStart([ABC],{"a".."z","A".."Z"}&{"-","."})) )

 

 

Here is my codes in 'advanced editor'.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkosy9Q1VIrViVYKrswrSc3RNTEC8wIy8lPzMit0jU3A3MDS1KJKXTNzMMcnPzElMy9dT08PSc43MS+zoDQnsSQ1BSyanJpXUpSYoxQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ABC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ABC", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type","Result",each if Text.Length (Text.TrimStart([ABC],{"a".."z","A".."Z"}&{"-","."}))=0 then 0 else Number.FromText(Text.TrimStart([ABC],{"a".."z","A".."Z"}&{"-","."})) )
in
    Custom1

 

 

Result:

b2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

I;d like to suggest you use Power Query. I created data to reproduce your scenario.

Table:

b1.png

 

You may add a new custom column with the following formula.

 

= Table.AddColumn(#"Changed Type","Result",each if Text.Length (Text.TrimStart([ABC],{"a".."z","A".."Z"}&{"-","."}))=0 then 0 else Number.FromText(Text.TrimStart([ABC],{"a".."z","A".."Z"}&{"-","."})) )

 

 

Here is my codes in 'advanced editor'.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkosy9Q1VIrViVYKrswrSc3RNTEC8wIy8lPzMit0jU3A3MDS1KJKXTNzMMcnPzElMy9dT08PSc43MS+zoDQnsSQ1BSyanJpXUpSYoxQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ABC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ABC", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type","Result",each if Text.Length (Text.TrimStart([ABC],{"a".."z","A".."Z"}&{"-","."}))=0 then 0 else Number.FromText(Text.TrimStart([ABC],{"a".."z","A".."Z"}&{"-","."})) )
in
    Custom1

 

 

Result:

b2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

rajulshah
Resident Rockstar
Resident Rockstar

Hello @Anonymous,

 

As you said you cannot touch MQuery, I have tried creating a calculated column as below:

Last Integer = 
VAR IfExists = SEARCH("-",SplitColumns[Value],1,-1)
VAR TotalLength = LEN(SplitColumns[Value])
VAR SplitInteger = IF(IfExists>0,RIGHT(SplitColumns[Value],TotalLength-IfExists),"0")
RETURN IFERROR(INT(SplitInteger),-2)

Please see the result below:
splitcolumns.PNG 

Hope this helps.

Anonymous
Not applicable

Would this work?

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkosy9Q1VIrViVYKrswrSc3RNTEC8wIy8lPzMit0jU3A3MDS1KJKXTNzMMcnPzElMy9dT08PSc43MS+zoDQnsSQ1BSyanJpXUpSYoxQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Inserted Text After Delimiter" = Table.AddColumn(Source, "Text After Delimiter", each Text.AfterDelimiter([Column1], "-", {0, RelativePosition.FromEnd}), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Inserted Text After Delimiter",{{"Text After Delimiter", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Text After Delimiter", 0}})
in
#"Replaced Errors"

 

 

So I first take the part after the first delimiter (-) starting from the right. Then I will get a column with numbers, and some with words. Then I convert to whole numbers, which will result in errors for the strings. Then I replace the errors with a 0, and voila.

 

There may be a better way to do this, but this seems to work so far.

 

Jaap

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors