Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
Solved! Go to Solution.
Hi, @Anonymous
I;d like to suggest you use Power Query. I created data to reproduce your scenario.
Table:
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
I;d like to suggest you use Power Query. I created data to reproduce your scenario.
Table:
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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:
Hope this helps.
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
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
93 | |
60 | |
43 | |
35 | |
34 |