Forum Discussion

Vladisam's avatar
Vladisam
Helper 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...
  • 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