Forum Discussion
Extraction of multiple 7 characters text substrings starting with "O-" from string of text
- 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" - Anonymous4 years ago
Ok check it out:
=Table.AddColumn("Orders", each List.Select(Text.Split([Column1], " "), each Text.StartsWith(_, "OI-")))
-- Nate
Totally agree - that seems to be very powerful. (BTW can it handle multiple substrings within same string?) I am lucky (for now) that substrings i need have spaces on both sides, but my users are unpredictable, so i am walking on thin ice.
This is waaay above my current level 😞 but definitely need learning.
And I need it in Service and I am not friends with R or Pythoon :(.
Vladisam few thing to clarify. ...the solution I gave you is neither coming from R nor from Python. I want to do everything natively as much as possible casue Python and R solution does not work in service. This is running javascript natively in PQ and handling regex through js natively in PQ.
Even though R has a similar library called stringR which I used to use for this sort of thing only to realize later that it would fail in service.
So be assured that whatever I am providing will fully evaluate in service as I have numerous regex extraction of mine running natively in my workspace.
Secondly, multiple substring I can try, but instaed can you please prepare a sample data, provide here and tag me back?
- smpa014 years ago
Community Champion
Vladisam did you mean mutiple substring like this? if yes, same soution will do the trick
If not, provide sample data
- Vladisam4 years ago
Helper II
Yes, this is what I am looking for - and it should be only unique values. But let me create sample data as well.
(Very cool Sales Calendar).
Sorry got confused with R - it's from Pat's post (above) regarding regex.
- Vladisam4 years ago
Helper II
Dupe- deleted:
- Vladisam4 years ago
Helper II
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