Extracting text from column using a test?

[Anonymous]

Hi everyone!

I have an odd request for you. My data set has a table with a "Notes" column. Free text, could be allsorts in there. But it is also used to make a note of our quote numbers, which all start with "#QTE" and are in the format "#QTE000000". I've created a conditional column to pull all examples of "#QTE", but each of these quote numbers is followed by EITHER an amount of quoted hours, OR a quoted value:

I need to pull the quoted value in hours (32.5) to one column, and the quoted value in pounds (675) to another column. (the symbol  before each £ is what I get in the raw data, can't be helped)

What's the easiest way of doing this? I've tried duplicating and extracting data by delimiter, but I can't find anything that doesn't cause errors in cells, or ends up extracting both values. Is there something I could do using conditional columns with parameters? I'm a noob who knows nothing of parameters yet....

Anyway, fingers crossed, I hope that all makes sense.

[Mariusz]

Hi @Anonymous 

Try something like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUg4McbW0tDQyMlLQVTjcdGixmbmpUqwOXMLQ0BAoYWykZ6qQkV9aVKwUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
            split = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)([Column1]),
            tranHrs= List.RemoveNulls( List.Transform( split, (i) => try Number.FromText( i ) otherwise null ) ),
            tranValue = List.RemoveNulls( List.Transform( split, (i) => try Number.FromText( Text.Replace( i, "£", "" ) )  otherwise null ) ),
            records = 
                [ 
                    hours = if Text.Contains( [Column1], "hours" ) then tranHrs{0} else null,
                    value = if Text.Contains( [Column1], "£" ) then tranValue{0} else null
                ]

        in 
            records
    ),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"hours", "value"}, {"hours", "value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"hours", type number}, {"value", type number}})
in
    #"Changed Type1"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[amitchandak]

Got to edit query/ transform data mode.  use this in custom column

https://docs.microsoft.com/en-us/powerquery-m/text-start

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous · Accepted solution]

Hi @amitchandak 

That didn't help in this situation, but thank you anyway. 

I've got a result by using conditional columns, splitting by delimiters, supressing errors etc, then when finally left with a column containing "£675" and another column containing "32.5 hours", I was able to extract only the numbers using the instructions in this article.

All I changed in the above was adding a period to the list so that my decimal point wasn't also removed.