Forum Discussion

pramita's avatar
pramita
Helper II
6 years ago

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

  • 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"
  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    Not 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:
    :