Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, Good day for All
I need more one help:
How to extract only numbers from a column
|column that I have | column that I need
There is no default, numeric characters can be anywhere in the text
Solved! Go to Solution.
Hey,
in the Query Editor add a custom column and use this formula
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
replace Column1 with the name of your column.
The idea, put the text of the source column to a list of characters, check each character if it is of type number, if yes keep it, else skip the character.
It's maybe not that obvious, but it's the pattern how regex would do it.
A little picture of my sample
Regards
Tom
Hey @MKI_GPCG ,
please provide a pbix that allows to recreate the error, upload the pbix to onedrive or dropbox and share the link.
Regards,
Tom
Hey @MKI_GPCG ,
you have to upload the xlsx as well, as without the datasource it's not possible to do any Power Query.
Please consider to add a parameter to the pbix that points to the directory where the xlsx is located and allows faster adjustment to different environments: https://docs.microsoft.com/en-us/power-query/power-query-query-parameters
Regards,
Tom
@FabrPrado wrote:Hi, Good day for All
I need more one help:
How to extract only numbers from a column
|column that I have | column that I need
There is no default, numeric characters can be anywhere in the text
This one does not tackle the decimal point. On the other hand the other solution does.
Hey,
in the Query Editor add a custom column and use this formula
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
replace Column1 with the name of your column.
The idea, put the text of the source column to a list of characters, check each character if it is of type number, if yes keep it, else skip the character.
It's maybe not that obvious, but it's the pattern how regex would do it.
A little picture of my sample
Regards
Tom
hi Tom,
Your code works perfectly. However, how can I revise your code so that there will be a space between 1 and 2 on the third row to show there are other texts between these 2 numbers in the original cell?
Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)))
Hello Tom,
Thanks for this code, it worked. but i got a small extension to this.
My data has decimals too, i mean
6
6.5 Nos
10.4 kgs etc
here i am getting
6
65
104.
Any help to make the decimal retain in the output.
Anyone with any suggestion? TIA.
Hey @Anonymous , maybe you want to try a simple Split by delimiter transformation
then
Tada
Hopefully, this is what you are looking for.
Regards,
Tom
Thank you Tom for helping it out.
Hello Tom,
Very helpful code you provided here. However, would it be possible for you to send a DAX version of this?
I am having difficulties trying to convert it from M to DAX.
Best regards,
Denis
Hey @denle ,
to create a calculated column you can use this DAX to extract the numbers from a string:
a Column with only numbers =
var __string = 'Table'[The text]
var __stringLength = LEN( __string )
var thenumbers =
CONCATENATEX(
FILTER(
ADDCOLUMNS(
ADDCOLUMNS(
GENERATESERIES( 1 , __stringLength , 1 )
, "@char" , MID( __string , ''[Value] , 1 )
)
, "isnumber" , IF( ISERROR( VALUE( [@char] ) ) , 0 , 1 )
)
, [isnumber] = 1
)
, [@char]
)
return
thenumbers
Here is a little screenshot:
Hopefully, this provides what you are looking for. Nevertheless, I recommend to extract the numbers using Power Query instead of DAX.
Regards,
Tom
Hey @MKI_GPCG ,
you have to be aware that my solution can only be used inside Power Query as is using M functions. I mention this because you are referencing DAX.
Please describe in much more detail where you are trying to use my formula please provide screenshots
Regards,
Tom
it works perfectly !
thanks a lot
Giavo, try this
Text.Select([SourceColumn], {"0".."9","."})
And a simple way to extract only numbers from a column, would be Text.Select([SourceColumn], {"0".."9"})
Very good, working;
Thank TomMartens
See how my table is:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |