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  did you mean mutiple substring like this? if yes, same soution will do the trick

 

 

 

 

 

 

 

If not, provide sample data

 

 

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

Here is a sample:

Rakflhmjfl: "Jywmkq", MU: 10563048 - Adotdmyg: "2021_QJL_QfamdApoHdxgnz_ApkmaNdnnmgf_QJU_ATT_Txwq_QA", MU: 26167032 - Cff: NMUOJ RU EOBNMIF
Gdr (0%)
Ylfkmpxj opghz(j) Mgkdwma Gldiimn Rauxjhofgh Ywdhiplo Cff - Ydlhgfl: damadj (Qfamdnpo) MU: 3101331 - Rakflhmjfl: damadj_IR_Umjtwdq_AR MU: 3245736 - Adotdmyg: damadj-OQ^Iplhz-fApo-Umjtwdq-Ipgf-Bfhdlyfhmgy -ARRwbdqj_ Jg-2104- Afwhld K2 YZV RUM RUY 26 O-2PQXF O-2PQQ1 MU: 5956202 - Mgjflhmpg Jlafl: YZV | BJI | Raxwhj 18+ | Alpjj Ufkmnf | AYQ | Afwhld | UN 360 | Idhmpgdw | Bfhdlyfhmgy Y1QYI59 O-2PQXF O-2PQQ1 MU: 19199044
Zwlxi Sygo - Uirovwr: ilxlig (Zwlxidyn) NL: 3101331 - Elfwroxgwr: ilxlig_BE_Lxgpbis_SE NL: 3245736 - Sinpixqv: ilxlig-BE-wSyn-Lxgpbis-wSynnwrdw-Iwoirqwoxvq-SEEbaisgXv- 2107-13140-Zjboxpbw-O3 UGJ ELN ELU 029 O-2O4G5 O-2O4G5 O-2O4G6 NL: 6703113 - Nvgwroxyv Xrlwr: UGJ | IXB | Eljbog 18+ | Srygg Lwfxdw | SUZ | Swbori | LM 360 | Bioxyvib | Iwoirqwoxvq U1OL28O O-2O4G5 O-2O4G6 NL: 21408844
  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Vladisam  what is the pattern we are extracting?

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

      "O-xxxxx" - total of 7 characters, starting with "O-'. Sample has 4 records, two of them don't have order numbers, i need unique order numbers only.

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        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"