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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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