Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table with a column for different urls like these
/
/person
/person?name=xyz
/person/123
/person/123?name=xyz
How can I extract it so it makes a new column like
/
/person
/person
/person
/person
So far I have this, and it removes the ? and all after that. But it doesn't handle /123. I don't know how to remove that.
= Table.AddColumn(#"Changed Type", "Table", each Text.Range([Document.request], 0, if(Text.PositionOf([Document.request], "?")=-1) then Text.Length([Document.request]) else Text.PositionOf([Document.request], "?")))
Does anyone know?
Thanks
Solved! Go to Solution.
I figured it out. First I find position of ? if exists and remove everything from it onwards.
Then I find last index of "/", and get the substring from that+1 to the end, and check if thats a number.
If it is, I get the substring from start to the above index-1, else use the above string.
I figured it out. First I find position of ? if exists and remove everything from it onwards.
Then I find last index of "/", and get the substring from that+1 to the end, and check if thats a number.
If it is, I get the substring from start to the above index-1, else use the above string.
Here's an even easier solution:
let
Source = Table,
#"Extracted First Characters" = Table.TransformColumns(Source, {{"Data", each Text.Start(_, 7), type text}})
in
#"Extracted First Characters"
Keep in mind that this is not dynamic; it'll break down if the text length changes. This is what I meant by replacing "person" with "person/":
let
Source = Table,
#"Replaced Value" = Table.ReplaceValue(Source,"person","person/",Replacer.ReplaceText,{"Data"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Data", each Text.BeforeDelimiter(_, "/", 1), type text}})
in
#"Extracted Text Before Delimiter"
Forget about the non-digit to digit--that won't work.
--Nate
You can modify the non-digit to digit to make it work. Just use a list of split characters like "/" and "?".
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0leK1QGSBalFxfl5yGz7vMTcVNuKyipkQX1DI2N0PpLCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Doc = _t]),
#"Split Column by Character Transition" = Table.SplitColumn(Source, "Doc", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"/", "?"}, c), {"/", "?"}), {"Doc", "Suffix"})
in
#"Split Column by Character Transition"
You could also replace values and replace "person" with "person/" and then split by the second "/".
--Nate
Can you show an example? I don't understand.
You can use the Split by Character Transition under the Split menu.
--Nate
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.