Forum Discussion

jereaallikko's avatar
jereaallikko
Helper III
6 years ago
Solved

How to simplify a column text

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

Need Help
Tips and Tricks
  • Smauro's avatar
    Smauro
    6 years ago

    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,

3 Replies

  • Smauro's avatar
    Smauro
    Solution Sage
    6 years ago

    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,

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    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

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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