Remove characters convert text to numbers

[abujouz86]

I have a column of data that includes for example "1 person" or "10/1". I want to remove the characters leaving only the number and if there is a / I want to add the number to the left to the number to the right (e.g. 10/1 = 11).

In excel I would write the formula this way:

=IF(ProdRouteTrans[CATEGORYID]="","",

VALUE(TRIM(IF(ISERROR(FIND("/",ProdRouteTrans[CATEGORYID])),

LEFT(ProdRouteTrans[CATEGORYID],2),

LEFT(ProdRouteTrans[CATEGORYID],

FIND("/",ProdRouteTrans[CATEGORYID])-1))))

+ IF(ProdRouteTrans[CATEGORYID]="","",

VALUE(TRIM(IF(ISERROR(FIND("/",ProdRouteTrans[CATEGORYID])),0,MID(ProdRouteTrans[CATEGORYID],

FIND("/",ProdRouteTrans[CATEGORYID])+1,2))))))

But when I write the dax in pbi I get an error message "Expressions that yield variant data-type cannot be used to define calculated columns." Can someone help me fix this formula? Thank you!

[AntrikshSharma]

@abujouz86  Try this:

Column =
VAR CurrentCategory = Abu[Category]
VAR AllAlphabets =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 65, 90, 1 ),
            "Alphabets", LOWER ( UNICHAR ( [Value] ) )
        ),
        "Alphabets", [Alphabets]
    )
VAR RemovePeople =
    SUBSTITUTE ( CurrentCategory, " people", "" )
VAR FilterValues =
    FILTER (
        ADDCOLUMNS (
            GENERATESERIES ( 1, LEN ( RemovePeople ), 1 ),
            "v", MID ( RemovePeople, [Value], 1 )
        ),
        [v] <> "/"
            && NOT [v] IN AllAlphabets
    )
VAR Result =
    SUMX ( FilterValues, INT ( [v] ) )
RETURN
    Result

[abujouz86]

Thank you - I replaced "Abu[category]". However I get the error message "Cannot convert value '' of type Text to type Number."

[AntrikshSharma]

@abujouz86  Please ignore the previous post, it didn't account for 15/1 correctly. I am attaching the file below my signature, try that:

Column =
VAR CurrentCategory = Abu[Category]
VAR AllAlphabets =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 65, 90, 1 ),
            "Alphabets", LOWER ( UNICHAR ( [Value] ) )
        ),
        "Alphabets", [Alphabets]
    )
VAR RemovePeople =
    SUBSTITUTE ( CurrentCategory, " people", "" )
VAR SlashPostion =
    SEARCH ( "/", RemovePeople, 1, 0 )
VAR Result =
    IF (
        SlashPostion > 0,
        VAR LeftValue =
            MID ( RemovePeople, 1, SlashPostion - 1 )
        VAR RightValue =
            MID ( RemovePeople, SlashPostion + 1, LEN ( RemovePeople ) - SlashPostion )
        VAR Result =
            INT ( LeftValue ) + INT ( RightValue )
        RETURN
            Result,
        INT ( RemovePeople )
    )
RETURN
    Result

[abujouz86]

This is excellent, thank you! It's not running on my table because there is an additional term "person" that wasn't in the previous sample data, I've put it below and these are all the possible combinations in the column. I'm sorry I didn't include it previously - I appreciate your help in this, thank you.

CATEGORYID

2 people

8/1 people

5/1 people

4/1 people

3 people

4 people

9/1 people

6/1 people

7/1 people

15/1 people

1 person

11/1 people

13/1 people

3/1 people

10/1 people

[AntrikshSharma]

@abujouz86  In that case you can try this: 

Column =
VAR CurrentCategory = Abu[Category]
VAR AllAlphabets =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 65, 90, 1 ),
            "Alphabets", LOWER ( UNICHAR ( [Value] ) )
        ),
        "Alphabets", [Alphabets]
    )
VAR RemovePeople =
    IF (
        CONTAINSSTRING ( CurrentCategory, "people" ),
        SUBSTITUTE ( CurrentCategory, " people", "" ),
        IF (
            CONTAINSSTRING ( CurrentCategory, "person" ),
            SUBSTITUTE ( CurrentCategory, " person", "" )
        )
    )
VAR SlashPostion =
    SEARCH ( "/", RemovePeople, 1, 0 )
VAR Result =
    IF (
        SlashPostion > 0,
        VAR LeftValue =
            MID ( RemovePeople, 1, SlashPostion - 1 )
        VAR RightValue =
            MID ( RemovePeople, SlashPostion + 1, LEN ( RemovePeople ) - SlashPostion )
        VAR Result =
            INT ( LeftValue ) + INT ( RightValue )
        RETURN
            Result,
        INT ( RemovePeople )
    )
RETURN
    Result

[abujouz86]

Amazing, thank you thank you thank you!!!

[AntrikshSharma]

@abujouz86 Can you provide some sample data?

[abujouz86]

CATEGORYID

2 people

2 people

8/1 people

8/1 people

8/1 people

8/1 people

2 people

5/1 people

5/1 people

4/1 people

3 people

4 people

4 people

4 people

9/1 people

5/1 people

8/1 people

6/1 people

8/1 people

8/1 people

5/1 people

7/1 people

8/1 people

8/1 people

15/1 people

[PaulDBrown]

@abujouz86 

I would suggest you do these types of transformations in Power Query, if possible.

[abujouz86]

How to do it in power query?

[PaulDBrown]

@abujouz86 

@AntrikshSharma  has provided an option using DAX. If you want to do it in Power Query...

The code for this example is:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Paul\OneDrive\Shared web\Comm PBIs\Transform Category ID PQ\Category ID.xlsx"), null, true),
    CATEGORYID_Table = Source{[Item="CATEGORYID",Kind="Table"]}[Data],
    #"Duplicated Column1" = Table.DuplicateColumn(CATEGORYID_Table, "CATEGROYID", "CATEGROYID - Copy"),
    #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"CATEGROYID", "Original"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns2","people","",Replacer.ReplaceText,{"CATEGROYID - Copy"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "CATEGROYID - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"CATEGROYID - Copy.1", "CATEGROYID - Copy.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"CATEGROYID - Copy.1", Int64.Type}, {"CATEGROYID - Copy.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Sum", each [#"CATEGROYID - Copy.1"] + [#"CATEGROYID - Copy.2"]),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Final result", each if [#"CATEGROYID - Copy.2"] = null then [#"CATEGROYID - Copy.1"] else [Sum]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"CATEGROYID - Copy.1", "CATEGROYID - Copy.2", "Sum"})
in
    #"Removed Columns"

And I've included the sample file

[PaulDBrown]

@abujouz86 

Can you provide a sample dataset?