How to simplify a column text

[jereaallikko]

Hi all,

I'm having a slight problem with organizing a messy data table, in Power BI Query. An example table shown below.

What I'm trying to do (preferably in Power BI Query), is to modify data in the Description column, to indicate clearly only the status percentage.

For example like this:

Description

Status: 30%

Status: 80%

Status: 20%

Status: 0%

Status: 100%

Status: 85%

Status: Unknown/?/null

Status: 10%

Status: ?/Unknown/null

Status: 0%

Can also be without colons (:) or percentage marks (%)

Can someone provide me step-by-step instructions, if it is possible?

Thanks

Jere

[mahoneypat]

You can add a Custom Column with an expression like this

= "Status: " & Text.Select(Text.BetweenDelimiters([Description], "Status", "%"), {"0".."9"}) & "%"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Pragati11]

Hi @jereaallikko ,

You can try using EXTRACT feature in query editor as follows:

There are multiple options in there. In your case you can try using "Text Between Delimiters" option.

Thanks,

Pragati

[Smauro · Accepted solution]

Hi @jereaallikko 

You could try adding in a function with the transformations, and transforming your column with it:

    StatFinder = (t1 as text) =>
        let
            t = Text.Replace(Text.Lower(t1), ":", " "),
            s1 = Text.AfterDelimiter(t, "status"),
            n1 =
                if s1 <> ""
                    then    
                        let t2 = Text.BeforeDelimiter(s1, "%"),
                            t3 = try Number.From(t2) otherwise try Number.From(Text.BeforeDelimiter(Text.AfterDelimiter(Text.Trim(s1), " "), " ")) otherwise null
                        in Text.From(t3)
                    else null,
            res = if n1 = null then "Status: Unknown" else Text.Trim("Status: " & n1 & "%")
        in
            res,
    FindStatus = Table.TransformColumns(PreviousStep,{{"Description",  StatFinder, type text}})

 (PreviousStep should be your last step)

Cheers,