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
Hi Vladisam ,
From this:
between 0 and 5 order numbers, and they start with “O-“
Order numbers are random alphanumeric combinations and have fixed length of 7.
I built a simply data sample:
Then split the column by Custom Delimiter like this:
Output:
If it is not your expected, please share more detail information to help us clarify your scenario.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Vladisam4 years ago
Helper II
Sorry I think I should've shared the sample. My challenge was to extract only unique values from the list, which is a column in the table (or get only unique values into the list in the first place). I got this to work:
Table.AddColumn(#"Added Custom", "Try1", each List.Distinct(List.FindText(Text.Split([Description]," "),"O-")))Thanks for reply, though.