Avatar of SQL Power BI
SQL Power BI
Flag for United States of America asked on

DAX, Power BI - How to extract number from alphanumeric text and sum up by creating a new column

Hi all,

I would like to extract the first numbers from the text below in Power BI. For example here is the text. How can I extract the numbers 1, 2, 4 and 11 from the string below and create a new column and sum them up?
1 enrolled, 0 waitlisted
2 of 8 enrolled 0 waitlisted
4 of 12 enrolled, 0 waitlisted
11 of 12 enrolled, 0 waitlisted

Thank you.

Power BI* DAX

Avatar of undefined
Last Comment
SQL Power BI

8/22/2022 - Mon
SQL Power BI

ASKER
It's interesting that it's paid service but no one wants to answer. This Experts Exchange is just waste of money
Cédric Tielemans

Not sure it's the best way to get help. Although possible in Dax I would do it in powerquery:
  1. Menu home > transform data
  2. Menu Add column > custom column
  3. Paste (replace "column1" with the name of your column):
List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)){0} 

Open in new window

   4. Close & load
   5. create a measure to sum


SQL Power BI

ASKER
I give an error message. It says Token comma expected by the last then and else statement. I tried adding comma but still same error

List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)){0}  
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SQL Power BI

ASKER
@Cédric Tielemans 

Thank you so much for the code. Never mind about the syntax error. But when I close and load it, the column shows an Error in each row for this particular column. Any ideas?

Thank you
SQL Power BI

ASKER
I also tried to create one calculated column to remove the text but to have the numbers but I still see the two letters "fu" in many of the rows. Now the challenge is how to remove the text from the numbers?
This removes the text after the 2nd index but not sure how to remove the text in the first and second position.

1 enrolled, 0 waitlisted
2 of 8 enrolled 0 waitlisted
4 of 12 enrolled, 0 waitlisted
11 of 12 enrolled, 0 waitlisted
Full, 0 waitlisted
ExtractNumber = MID('table'[column1],1,2)
Resutl:
1
2
4
11
Fu

Then I tried this to display all numbers and if it finds the text "Full" then it should give me the numbers from another column else the result column with numbers from above but it says variant type error cannot combine int with text. Any ideas?

ExtractNumber_2 = IF(CONTAINS('table'[column1],"Full",
                     ('table'[Column2]),
                     MID('table'[column],1,2)

Thank you

Cédric Tielemans

Not sure why you get an error if you followed the steps.Can you post your Mcode? I tested the function in this example pbix file: extract nrs.pbix and it works without problem.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SQL Power BI

ASKER
This work fine but I need to use another column value in place of the last zero {0}. How can I achieve that?
List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)){0}
The result
1
2
4
11
Fu

But I need something like this. For Example. I get all the values but when it find the "full 0 waitlisted" it should replace it with the value from Column2 below.
List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null)){Column2}
The desired result
1
2
4
11
20 ----------- This value should come from Column2 but it's zero.

Any Ideas?
SQL Power BI

ASKER
Let's say that code worked and I get the following result.
1
2
4
11
0
Now I would like to check if the value of column1 is zero. I need to replace it with the values of coulmn2 else column1 using this DAX code below but it does not work. It says "Expressions that yield variant data-type cannot be used to define calculated column.

Column3 = IF(VALUE(table[Column1]) = 0, ‘table’[column2], ‘table’[column1])

I want the result like this:
1
2
4
11
20 - instead of zero. This is the value coming from column2
Cédric Tielemans

I would just add it to the PowerQuery function:
if List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null )){0} <> "0" then List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null )){0} else [Column2]

Open in new window

Anyway also in DAX you'll need to specify the 0 between double quotation marks as the result of this function will be text => "0" Or you'll need to change the datatype first.

If the provided answer did solved your initial request you should accept it as an answer so others know it's solved.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SQL Power BI

ASKER
The one you provided didn't work for more than two digit numbers for example 10, 12, 136 etc. The code works for single digit numbers but it deletes the second digits from the numbers above. How can you achieve this if the column2 values are 12, 20, 100, 2000 etc?
= if List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null )){0} <> "0" then List.RemoveNulls(List.Transform(Text.ToList([Column1]),each if Value.Is(Value.FromText(_), type number) then _ else null )){0} else [Column2]

SQL Power BI

ASKER
For example : "137 of 175 enrolled, 0 waitlisted". It shows 1 instead of 38 because 38 is the difference 137 - 175 = -38

Thank you so much in advance!
SQL Power BI

ASKER
Sorry..correction. It should show 137 because those number of students are enrolled
For example : "137 of 175 enrolled, 0 waitlisted". It shows 1 instead of 137

Thank you so much in advance!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SQL Power BI

ASKER
Lets say colum1 = "65 of 175 enrolled, 0 waitlisted" column2 = 175. The result custom column should show 65 but now it shows 6 and it deletes the second digit 5.
Any ideas?
Cédric Tielemans

ok you are right, changed the approach by first splitting the col and than just keep all numbers. To reproduce I used the "Split column by digit" menu and then edited the M manually so I just keep the col you need adding the custom col in the next step.

Below the Mcode ot the test:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlEwNFRIzEtRMFbSUTJUitWBihoBuUZgrpEBWN4UKGAMFshOScvKUTBQSMlOy0rJyU4DqTRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Character Transition", "Custom", each if Text.Select([Column1], {"0".."9"}) <> "0" then Text.Select([Column1], {"0".."9"}) else [Column2])
in
    #"Added Custom"

Open in new window

SQL Power BI

ASKER
Great. Which columns to split and what digits I should add?. Can you please give me step by step instructions?

I do really appreciate your help.

Thank you
Your help has saved me hundreds of hours of internet surfing.
fblack61
Cédric Tielemans

The column to split is the one containing your "text number" string. in PQ editor:
  1. Left click on that column
  2. Choose menu Home > split column > "By digit to non digit
The split function will add a number notation to your column splitting it to multiple columns. As you don't need them replace this part (at the end) of the function in the formula or advanced editor e.g {"Column1.1", "Column1.2"}) => just keep the original column name e.g. {"Column1"}).
Note: if you do want to keep the original column duplicate it first (right click on the column > duplicate)

   3. Choose Add column > custom column
   4. Paste (replace "column1" & "column2" with the name of your columns):
if Text.Select([Column1], {"0".."9"}) <> "0" then Text.Select([Column1], {"0".."9"}) else [Column2]

Open in new window

SQL Power BI

ASKER
Ok great. Which M code do you want me to use in when i split the column? This one below or the last one?
let     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlEwNFRIzEtRMFbSUTJUitWBihoBuUZgrpEBWN4UKGAMFshOScvKUTBQSMlOy0rJyU4DqTRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),     #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1"}),     #"Added Custom" = Table.AddColumn(#"Split Column by Character Transition", "Custom", each if Text.Select([Column1], {"0".."9"}) <> "0" then Text.Select([Column1], {"0".."9"}) else [Column2]) in     #"Added Custom"




Last one- M code

if Text.Select([Column1], {"0".."9"}) <> "0" then Text.Select([Column1], {"0".."9"}) else [Column2]
Cédric Tielemans

if you follow the step by step instructions you don't need to copy any M?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SQL Power BI

ASKER
Thank you so much as always for your help but still not working. Maybe I should explain more in the screen shot below.
This code works fine but it does not pull the "Column2" Value. For example if the column2 value is "Full. 0 waitlsted" then in Column 3 on the right it should show the value of 48 from column1 but it shows 8 and also instead of 8 again it should show 16 from the left column to the right. Those two rows are wrong so I'm not sure how to show the actual value of left most column in right most column when the middle column says. "Full, 0 waitlisted" Any ideas?

if Text.Select([Column1], {"0".."9"}) <> "0" then Text.Select([Column1], {"0".."9"}) else [Column2]


Cédric Tielemans

I have no clue how this function can show "8" if their is no "8" in your text col. otherwise send an extract ( a few lines) in a pbix file and I'll have a look.
SQL Power BI

ASKER
I tried to change the M code like this but still same issue. I split the column and some rows have "Full,0" and some rows have digits. The third column on the right in the screen shot is the custom column
 
 
if Text.Select([#"Column2 in the middle - Copy.1"], {"0".."9"}) <> "0" or Text.Select([#"column2 in the middle- Copy.1"], {"a".."z"}) = "Full, 0" then Text.Select([#"column2 in the middle - Copy.1"], {"0".."9"})
else [ Column on left ] -- This should display values in the last column which is custom column the third column


I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SQL Power BI

ASKER
I really need to finish this today. Once its fully working then I will accept the solution and close the ticket. I do really appreciate your help.
Thank you
SQL Power BI

ASKER
It does not find "Full, 0"

 
Maximum EnrollmentsCurrent Registration StatusCustomColumn
100Full, 0 waitlistedWrong value here
50Full, 0 waitlistedWrong value here
45Full, 0 waitlistedWrong value here
353, of 35 enrolled3
655 of 65 enrolled5
128 of 12 enrolled8
10Full, 0 waitlistedWrong value here
42 of 4 enrolled2
84 of 8 enrolled4

SQL Power BI

ASKER
Should be like this below:

 
Maximum EnrollmentsCurrent Registration StatusCustomColumn
100Full, 0 waitlisted100
50Full, 0 waitlisted50
45Full, 0 waitlisted45
353, of 35 enrolled3
655 of 65 enrolled5
128 of 12 enrolled8
10Full, 0 waitlisted10
42 of 4 enrolled2
84 of 8 enrolled4




Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SQL Power BI

ASKER
I tested the example table above and it works fine but now sure in the real project it's not working? DO I need to clear the Power BI Cache and any other ideas?

I really need to submit this on this Monday.

Thank you
Cédric Tielemans

The function I provided should do what you want. Provide a pbix file and I'll have a look.
SQL Power BI

ASKER
Can you fix this code below. I'm not sure how to add each step individually. I copied and pasted the whole query in M code but it does not work.

= #"Duplicated Column" = Table.DuplicateColumn(#"Removed Duplicates", "Current Registration Status", "Current Registration Status - Copy",
#"Split Column By Delimiter" = Table.SplitColumn(#"Duplicated Column", "Current Registration Status - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Current Registration Status - Copy.1", "Current Registration Status - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column By Delimiter",{{"User ID", type text}, {"Supervisor ID", type text}, {"Current Registration Status - Copy.1", type text}, {"Current Registration Status - Copy.2", type text}}),
 
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","Full,","0",Replacer.ReplaceText,{"Current Registration Status - Copy.1"}),
 
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Custom", each if [#"Current Registration Status - Copy.1"] = "0" then [Maximum Enrollments] else [#"Current Registration Status - Copy.1"]),
 
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Current Registration Status - Copy.2"}),
 
#"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom", Int64.Type}})
)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SQL Power BI

ASKER
I'm really running out of time. Can you fix this one below?

#"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows1", "Current Registration Status", "Current Registration Status - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Current Registration Status - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Current Registration Status - Copy.1", "Current Registration Status - Copy.2", "Current Registration Status - Copy.3", "Current Registration Status - Copy.4"}),
   #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition","Full, 0", "0", Replacer.ReplaceText,{"Current Registration Status - Copy.1"}),
#"Added Custom3" = Table.AddColumn(#"Replaced Value", "CustomColumn_5", each if Text.Select([#"Current Registration Status - Copy.1"], {"0".."9"}) <> "0" then Text.Select([#"Current Registration Status - Copy.1"], {"0".."9"})
else [Maximum Enrollments])
in
    #"Added Custom3"
Cédric Tielemans

the replace "full 0" step is not needed:
    #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows1",
, "Current Registration Status", "Current Registration Status - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Current Registration Status - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Current Registration Status - Copy.1"}),
   #"Added Custom3" = Table.AddColumn(#"Split Column by Character Transition", "CustomColumn_5", each if Text.Select([#"Current Registration Status - Copy.1"], {"0".."9"}) <> "0" then Text.Select([#"Current Registration Status - Copy.1"], {"0".."9"})
else [Maximum Enrollments])
in
    #"Added Custom3" 

Open in new window


The result:

SQL Power BI

ASKER
Hi Cedric,

Thank you so much for your efforts but it does not work on my project data set. Do I still need this custom column code and also the code you sent should be in the advanced Editor?



  #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows1", , "Current Registration Status", "Current Registration Status - Copy"),     #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Current Registration Status - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Current Registration Status - Copy.1"}),    #"Added Custom3" = Table.AddColumn(#"Split Column by Character Transition", "CustomColumn_5", each if Text.Select([#"Current Registration Status - Copy.1"], {"0".."9"}) <> "0" then Text.Select([#"Current Registration Status - Copy.1"], {"0".."9"}) else [Maximum Enrollments]) in     #"Added Custom3" 
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Cédric Tielemans

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SQL Power BI

ASKER
This code for custom column should also be there as well right?

Also how to group by on Class Id's using DAX? Some of the Calculations are wrong in my project using your code. I want to use Group by on Class Id's in DAX and that might fix the maximum enrollment sum issues?
SQL Power BI

ASKER
I think the calculations are coming wrong because of the Course ID's are not being grouped. How can I achieve if I have the following courses: The CustomColumn_5 should show 16 but it's showing 8.

Course Id     Course Title    Course Level     Maximum Enrollments  Current Registration Status      CustomColumn_5
16000           Java                200                     16                                  Full, 0 Waitlisted                   16 instead it shows 8
16000           Java                300                      16                                 Full, 0 Waitlisted                     16 instead it shows 8
16000           Java                400                       8




Cédric Tielemans

Keep questions focussed for future reference. If the provided answer solved your initial problem accept it an create a separate question about your grouping issue.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SQL Power BI

ASKER
Ok. I will accept your solution. I will create a new question for grouping Issue. I hope you resolve the grouping issue in the next question.

Thank you so much.
SQL Power BI

ASKER
@Cedric Tielemens,

Can you please answer my other question I posted on Power BI grouping? I'm running out of time and my project has been delayed and really need help figuring this out.

Thank you.