Forum Discussion
Vladisam
4 years agoHelper II
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...
- 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
Anonymous
4 years agoNot applicable
Ok check it out:
=Table.AddColumn("Orders", each List.Select(Text.Split([Column1], " "), each Text.StartsWith(_, "OI-")))
-- Nate
- Vladisam4 years agoHelper II
That works - thanks a lot! So I have two valid solutions for my question - Power BI community is awesome!
For those who will be looking at this post later for their purposes - in answer above replace [Column1] with [Description] and "OI-" with "O-".