When working with cloud data sources you might encounter links where spaces show up as “%20” instead for example. This is called URI escaping or encoding. In Power Query there is a native function that does this kind of encoding: Uri.EscapeDataString – PowerQuery M | Microsoft Learn
However, a native function that does just the opposite is missing. Fortunately, this can be achieved with a custom function that I have found in the Power BI forum:
Uri.UnescapeDataString
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let func = | |
| (data as text) as text => | |
| let | |
| DecodedText = Uri.Parts("http://contoso?a=" & data)[Query][a] | |
| in | |
| DecodedText , | |
| documentation = [ | |
| Documentation.Name = " Uri.UnescapeDataString ", | |
| Documentation.Description = " Decodes special characters in the input data according to the rules of RFC 3986. ", | |
| Documentation.LongDescription = " Decodes special characters in the input data according to the rules of RFC 3986. ", | |
| Documentation.Category = " UriFunctions ", | |
| Documentation.Source = " www.TheBIcountant.com . ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Source: https://community.fabric.microsoft.com/t5/Desktop/How-to-convert-these-encode-url-to-normal-text/td-p/2388189 ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " Uri_UnescapeDataString(""Hello%20World"") ", | |
| Result = " ""Hello World"" | |
| "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
I hope it helps you as it did me.
Enjoy and stay queryious đ
Pingback: Decoding URIs in Power Query – Curated SQL