Forum Discussion
How to decode URL component using M Language?
I have a column with links in it, I need to decode it. The links are encoded, in %s as shown below
For example: https%3A%2F%2Fwww.rnd.de%2Fpanorama%2Fzwei-flugzeuge-stossen-im-munsterland-zusammen-beide-piloten-tot-YDHGMRPATRCUBC6SCDD27TSQRU.html
should be decoded to
https://www.rnd.de/panorama/zwei-flugzeuge-stossen-im-munsterland-zusammen-beide-piloten-tot-YDHGMRPATRCUBC6SCDD27TSQRU.html
I found a solution here:
https://stackoverflow.com/questions/36242695/how-to-decodeuricomponent-ex-2f3f263d
But I am very new to M code so I don't know where I'm going wrong. But it gives me this error
This is the code I have written
let
InputData = Excel.Workbook(File.Contents("C:\Users\Fatima\Downloads\Microsoft.SkypeApp_kzf8qxf38zg5c!App\All\encoded-URLs.xlsx"), null, true),
Uri.UnescapeDataString = (data as text) as text => let
ToList = Text.ToList(data),
Accumulate = List.Accumulate(ToList, [ Bytes = {} ], (state, current) =>
let
HexString = state[HexString]?,
NextHexString = HexString & current,
NextState = if HexString <> null
then if Text.Length(NextHexString) = 2
then [ Bytes = state[Bytes] & Binary.ToList(Binary.FromText(NextHexString, BinaryEncoding.Hex)) ]
else [ HexString = NextHexString, Bytes = state[Bytes] ]
else if current = "%"
then [ HexString = "", Bytes = state[Bytes] ]
else [ Bytes = state[Bytes] & { Character.ToNumber(current) } ]
in
NextState),
FromBinary = Text.FromBinary(Binary.FromList(Accumulate[Bytes]))
in
FromBinary,
AddEscaped = Table.AddColumn(InputData, "Escaped", each Uri.EscapeDataString([Column1])),
AddUnescaped = Table.AddColumn(AddEscaped, "Custom", each Uri.UnescapeDataString([Escaped]))
in
AddUnescaped
Can anyone tell me where I'm going wrong?
2 Replies
- lbendlinSuper User
Don't forget that there is always the brute force method. After all there are not that many encoded characters in the majority of URLs.
You can extend the below to also cover ? and & and space.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DYjLCsIwEAD/pdDj5lBBz7VFvQjax0FKD5GsbSCbhGZDIF9vYA4zsyzVzuxDfWrr5lZIKYnDKqGwhJfWHZJk0ZxQw8/ELWPcEAK7ENCCJqBoA+NhpFWQY5BE5X9RKwSvjeNS7Bg+/eP+HF7tNHTztTuPXd83l2l8D7PYmUy1rn8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"%3A",":",Replacer.ReplaceText,{"Column1"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","%2F","/",Replacer.ReplaceText,{"Column1"}) in #"Replaced Value1" - AllisonKennedyCommunity ChampionNot quite as elegant as decoding, but you could simply select the column, then do a replace values to find:
%2F
and replace with:
/
And do that again for
%3A
and replace with:
: