Forum Discussion

Vladisam's avatar
Vladisam
Icon for Helper II rankHelper II
4 years ago
Solved

Extraction of multiple 7 characters text substrings starting with "O-" from string of text

Hi,   I have a column [Description] where in each row string of text may contain few order numbers (between 0 and 5 order numbers, and they start with “O-“) that I have to extract. I found starting...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    Vladisam  there you go

     

     

    let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/Extraction-of-multiple-7-characters-text-substrings-starting/m-p/2117172#M62409"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='bodyDisplay_8'] > DIV.lia-message-body-content > TABLE:nth-child(3) > * > TR > :nth-child(1)"}}, [RowSelector="DIV[id='bodyDisplay_8'] > DIV.lia-message-body-content > TABLE:nth-child(3) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}}),
    fx = let   fx =(input)=>
    Web.Page(
        "<script>
            var x='"&input&"';
            var b = x.match(/O[-][0-9A-Za-z]{5}/gm);
            document.write(b);
        </script>"){0}[Data]{0}[Children]{1}[Children]    
in
    fx,
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fx([Column1])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Text"}, {"Text"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each List.Distinct(Text.Split([Text],","))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Text"})
    
in
    #"Removed Columns"

     

     

     

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Ok check it out:

     

    =Table.AddColumn("Orders", each List.Select(Text.Split([Column1], " "), each Text.StartsWith(_, "OI-")))

     

    -- Nate

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

Vladisam  sorry coluld not reply you earlier.

For the fx part, I have written a custom function using js script to be invoked later in the PQ later. I wrote a blog post earlier which gives you a basic understanding of how JS can run in PQ.

https://community.powerbi.com/t5/Community-Blog/How-to-use-JavaScript-inside-power-query-for-data-extraction/ba-p/1632844

I am planning to write the next series of this on regex extraction soon.

Inside fx, I am using JS match function where you can pass on regex expression 

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/match

 like following

 

<string>.match(regexp)

 

and it matches all instances of qualified patterns defined by the user in the string.

 

 

Vladisam's avatar
Vladisam
Icon for Helper II rankHelper II
4 years ago

This is awesome. Keep it coming - share the wealth 😉 (next posts)