Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I was wondering if anyone knew of a more elgant way to remove numbers from a string. I realized that Power BI does not support regular expressions. I know I could use R or another method to remove, but I am working on training users that will primarily only be using Power BI and Excel for simple data analysis.
MeterType = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FileToBeCleaned[itype],"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")
Does not work 😞
MeterType = SUBSTITUTE(ToBeCleaned[itype],%[0-9]%,"")
Solved! Go to Solution.
In the Query Editor you can either add a column with formula:
= Text.Combine(List.RemoveItems(Text.ToList([TextWithDigits]),{"0".."9"}))
Or transform the column: first choose Transform - Clean (or some other option) and then adjust the code in the formula bar to:
= Table.TransformColumns(PreviousStep,{{"TextWithDigits", each Text.Combine(List.RemoveItems(Text.ToList(_),{"0".."9"}))}})
the adjustment is the part starting with "each".
Thanks for kudos, guys, but - shame on me - today I was triggered by a question from @MattAllington on MrExcel.com involving Text.Remove, which can be used instead of my creative formula, which can be simply replaced by:
Text.Remove([TextWithDigits],{"0".."9"})
Hi, I'm new to PowerBI and SQL. I have used your recommended formula to remove numbers from a text string but PowerBI is giving me an error. What am I doing wrong ? thanks
I reinput the command and it worked !!!
@MarcelBeug Thanks for the Update!
I will actually use this in place of Step 2 here
This Step 2 seems to remove everything after a number - so if a cell starts with a number it will be left empty
= Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Start(_, Text.PositionOfAny(_,Text.ToList("0123456789"))), type text}})
In the Query Editor you can either add a column with formula:
= Text.Combine(List.RemoveItems(Text.ToList([TextWithDigits]),{"0".."9"}))
Or transform the column: first choose Transform - Clean (or some other option) and then adjust the code in the formula bar to:
= Table.TransformColumns(PreviousStep,{{"TextWithDigits", each Text.Combine(List.RemoveItems(Text.ToList(_),{"0".."9"}))}})
the adjustment is the part starting with "each".
You can use R to run a regular expression over your column if that helps.
If you give me your preferred Regex pattern I can try and get it going for you.
The R column in Power Query might look like this :
Just replace Column1 with your own column. This will append on a new column stripped of numbers.
This allows you to extend your regular expression to handle other patterns, so could be quite versatile
pattern<-"[A-Z]+" output <-within(dataset,{RegExCol=sapply(sub('[0-9]+', '', dataset$Column1), function(x) toString(x)) })
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |