Text extraction

[V24]

I have 2 types of inputs in my column:

1st: <div class=“External”><div style=“font-family”;”> Required text to be extracted</div></Div>

2nd: <div class =“External”> Required text to be extracted</div>

How do I extract the required text when the column has the above kind of rows as inputs

[dufoq3]

@Hi @V24, possible via UI

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBOTsksU0jOSSwutn3UMMe1oiS1KC8x51HDXLBsKkJNcUllTipITVp+XoluWmJuZk4lUJk1XKlCUGphaWZRaopCSWpFiUJJvkJSqgKQVZSYXJKaAjFIH2gSssH6LjC+UqwOhosUsDqJNHuUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Source, {{"Column1", each Text.BeforeDelimiter(_, "</div>"), type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Column1", each Text.AfterDelimiter(_, ">", {0, RelativePosition.FromEnd}), type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Column1", Text.Trim, type text}})
in
    #"Trimmed Text"

[V24]

Hi thanks a ton for your solution. I am new to powerBI. Just a quick question: I did not understand the source part. What should I replace here? And can I directly use this code if so can I just paste it in M code and is there any other thing I need to change apart from source and column?

[dufoq3]

@V24,

save your query as MyData for example. Create new blank query, open it in Advanced editor. Delete whole code and paste there the one I created. Edit 1st step Source = and delete selected part (see picture below). Replace whole this part with MyData so Source = MyData, (don't forget comma at the end as you see in the picture)

[slorin]

Add a new step with the above formulas by replacing Your_Source and Your_Column with previous step and column name

Stéphane

[slorin]

You're on the Power Query forum, not the DAX forum

Stéphane

[V24]

Sorry, that is not what I meant. I want to edit it in the power query itself. Is there any M code or something which can help me get the desired output?

thanks Stéphane

[slorin]

Hi,

= Table.TransformColumns(Your_Source, {{"Your_Column", 
    each Text.BetweenDelimiters(_, ">", "<", {Text.Length(Text.Select(_,"<"))/2-1, RelativePosition.FromStart}), type text}})

or

= Table.TransformColumns(Your_Source, {{"Your_Column",
   each Text.AfterDelimiter(Text.BeforeDelimiter(_, "</"), ">", {0, RelativePosition.FromEnd}), type text}})

Stéphane 

[V24]

Hi, tried using this syntax to create a new column. It threw an error : A cyclic reference was encountered during evaluation.

Also to be clear: I have both the above types in my column. Want to extract the specific text from both the types using a single dax command. Thank you for your help.