Forum Discussion
Helper IIExtraction of multiple 7 characters text substrings starting with "O-" from string of text
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"
Ok check it out:
=Table.AddColumn("Orders", each List.Select(Text.Split([Column1], " "), each Text.StartsWith(_, "OI-")))
-- Nate
Helper II"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.
Community ChampionVladisam 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"
- smpa01
Vladisam sorry coluld not reply you earlier.
For the fx part, I have written a custom function using js script to be invoked later in the PQ later. I wrote a blog post earlier which gives you a basic understanding of how JS can run in PQ.
I am planning to write the next series of this on regex extraction soon.
Inside fx, I am using JS match function where you can pass on regex expression
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/match
like following
<string>.match(regexp)and it matches all instances of qualified patterns defined by the user in the string.
- Vladisam
Bingo!
Thank you so much!! RegEx made it to the top of my "Learn Next" list!
Can you pleasee comment on how code works (from "fx = let" down)?
- Vladisam
This is awesome. Keep it coming - share the wealth 😉 (next posts)
- smpa01