Forum Discussion
Extracting Text Before a Delimiter When Having 2 Delimiters
Hi! I am fairly new to Power BI. I have a column that contains a list of URLs. Some URLs have text that I need to remove. The text that I need removed will either start with "#" or "?". I am wanting to get the Clean URL. This was a great resource however, it is only for 1 delimiter. Can someone help me figure out how to add "?" to my code?
Example of URL structure:
www.urlexample.com/facility/services/#about
www.urlexample.com/facility/?utm_source_utm_campaign
Clean Entry Page URL =
LEFT(
CardiologyPagePerformance[Entry Page URL],
SEARCH(
"#",
CardiologyPagePerformance[Entry Page URL],
,
LEN(CardiologyPagePerformance[Entry Page URL]) +1
) -1
)
As argument in Custom Column Dialog:
Splitter.SplitTextByAnyDelimiter({"#","?"})([Column1]){0}Example from Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi8v1ystykmtSMwtyEnVS87P1U9LTM7MySyp1C9OLSrLTE4t1ldOTMovLVGK1cGv3r60JDe+OL+0KDk1HsRMBqpJzEzPU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" =Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByAnyDelimiter({"#","?"})([Column1]){0}, type text) in #"Added Custom"
1 Reply
As argument in Custom Column Dialog:
Splitter.SplitTextByAnyDelimiter({"#","?"})([Column1]){0}Example from Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi8v1ystykmtSMwtyEnVS87P1U9LTM7MySyp1C9OLSrLTE4t1ldOTMovLVGK1cGv3r60JDe+OL+0KDk1HsRMBqpJzEzPU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" =Table.AddColumn(#"Changed Type", "Custom", each Splitter.SplitTextByAnyDelimiter({"#","?"})([Column1]){0}, type text) in #"Added Custom"