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

Extracting text and numerical types from one column and splitting into two columns

I have an interesting problem I am hoping the community can solve.

 

The first part of the problem is how do I split one column into two columns dependant on the data type i.e.:

I have this flattened view:

 

AttributeValues
Risk Score3.33
CoverageYes
Research CompletedNo
% of Executives5%
Five Year Growth3%
Emissions12,000

 

I would prefer the below unless someone can solve the second potential issue.

 

AttributeValuesText ValuesNumerical Values
Risk Score3.33 3.33
CoverageYesYes 
Research CompletedNoNo 
% of Executives5% 5%
Five Year Growth3% 3%
Emissions12,000 12,000

 

My second potential issue is that if I am left with only the value column, how do I get the SUM function to work.

When there are mixed type of data it does not work, is there any work around so it only counts the numerical rows?

 

Best Regards,

1 ACCEPTED SOLUTION

After trying all the methods the most workable solution was as follows:

1. Duplicate the column you want to split

2. Change the duplicated column to a numerical type column

3. Replace any errors with nulls

4. Create a conditional column that states where the numerical type column is 'null' then take the value from the original column.

View solution in original post

7 REPLIES 7

After trying all the methods the most workable solution was as follows:

1. Duplicate the column you want to split

2. Change the duplicated column to a numerical type column

3. Replace any errors with nulls

4. Create a conditional column that states where the numerical type column is 'null' then take the value from the original column.

Just FYI, this is nearly the same as what I suggested except that I did the equivalent of 1,2,3 as single step, #"Added Numerical Column".

v-chenwuz-msft
Community Support
Community Support

Hi @knowledgegarage ,

 

Is your problem being solved. If so, please mark it as a solution so that more people can see it.

 

Best Regards

Community Support Team _ chenwu zhu

AlexisOlson
Super User
Super User

This can be done pretty simply in the Query Editor using the Number.FromText() function. Anything that throws an error when trying to convert to a number we can consider as text. To get the numerical column, this should be sufficient as a new custom column:

try Number.FromText([Values]) otherwise null

 

Sample query you can paste into the Advanced Editor of a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY3BCsIwEER/ZVnoLUg0+AWlevNQTyXkUOJqg9aVbNr6+SbxOPPeMNZiH+QJV8+RUKHZGYNOWWx5pTg+SjeQ1KonoTH6CVqePy9KdMvwwpU1wHfovuSXFNbsKzw2FZxyhCHv4Bx5S1P5+JNuDiKB30XeH5TWGp37AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Attribute = _t, Values = _t]),
    #"Added Numerical Column" = Table.AddColumn(Source, "Numerical", each try Number.FromText([Values]) otherwise null, type number),
    #"Added Text Column" = Table.AddColumn(#"Added Numerical Column", "Text", each if [Numerical] = null then [Values] else null, type text)
in
    #"Added Text Column"
tamerj1
Super User
Super User

Hi @knowledgegarage 
You can use dax to create two calculated columns as follow https://www.dropbox.com/t/qOO0oQXdjDWteLOH

1.png2.png

Text Value = 
VAR T1 = UNION ( GENERATESERIES ( 0, 9, 1 ), { ".", "%", "," } )
VAR String = Data[Values]
VAR Length = LEN ( String )
VAR T2 = GENERATESERIES ( 1, Length, 1 )
VAR T3 = ADDCOLUMNS ( T2, "@Letter", MID ( String, [Value], 1 ) )
VAR T4 = FILTER ( T3, NOT ( [@Letter] IN T1 ) )
RETURN
    CONCATENATEX ( T4, [@Letter] )
Numerical Values = 
IF (
    Data[Values] <> Data[Text Value],
    VAR String = Data[Values]
    VAR Length =  LEN ( String )
    RETURN
        IF (
            CONTAINSSTRING ( String, "%" ),
            DIVIDE ( VALUE ( MID ( String, 1, Length - 1 ) ), 100 ),
            VALUE ( String )
        )
)

No. It was like so:

knowledgegarage_0-1659189483716.png

 

I want to either split it into two columns like so unless there is another way to calculate.

 

knowledgegarage_1-1659189558798.png

 

tamerj1
Super User
Super User

HI @knowledgegarage 

What was the original shape of the column? Something like "Risk Score 3.33" or additionaly there are brackets/special characters? Would you mind a DAX solution?

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.