How to convert these encode url to normal text from API By M language

[Tiger2514555]

My source:

let
Source = Json.Document(Web.Contents("http://192.168.99.46:8097/api/Mrr")),
#"Converted to Table" = Table.FromList(
Source,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Expanded Column1" = Table.ExpandRecordColumn(
#"Converted to Table",
"Column1",
{
"mrr_num",
"item",
"create_date",
"qty_mrr",
"raisedByID",
"raisedByName",
"failure",
"problemdescription",
"cause",
"causeOfDefact",
"ref_type",
"ref_num",
"ref_line",
"ref_release",
"entity",
"dispositionSerialNumber",
"dispositionFeature",
"dispositionFaultCode",
"dispositionCauseCode",
"dispositionQuantity",
"disposition",
"dispositionNote",
"lot_num",
"uf_ApprovalStatus",
"recordDate",
"unitcost",
"wiptotal",
"productName",
"suffix",
"status",
"closeDate",
"dispositionDate"
},
{
"mrr_num",
"item",
"create_date",
"qty_mrr",
"raisedByID",
"raisedByName",
"failure",
"problemdescription",
"cause",
"causeOfDefact",
"ref_type",
"ref_num",
"ref_line",
"ref_release",
"entity",
"dispositionSerialNumber",
"dispositionFeature",
"dispositionFaultCode",
"dispositionCauseCode",
"dispositionQuantity",
"disposition",
"dispositionNote",
"lot_num",
"uf_ApprovalStatus",
"recordDate",
"unitcost",
"wiptotal",
"productName",
"suffix",
"status",
"closeDate",
"dispositionDate"
}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Expanded Column1",
{
{"mrr_num", type text},
{"item", type text},
{"create_date", type datetime},
{"qty_mrr", Int64.Type},
{"raisedByID", Int64.Type},
{"raisedByName", type text},
{"failure", type text},
{"problemdescription", type text},
{"cause", type text},
{"causeOfDefact", type text},
{"ref_type", type text},
{"ref_num", type text},
{"ref_line", Int64.Type},
{"ref_release", Int64.Type},
{"entity", Int64.Type},
{"dispositionSerialNumber", Int64.Type},
{"dispositionFeature", type text},
{"dispositionFaultCode", type text},
{"dispositionCauseCode", type text},
{"dispositionQuantity", Int64.Type},
{"disposition", type text},
{"dispositionNote", type any},
{"lot_num", type text},
{"uf_ApprovalStatus", type text},
{"recordDate", type datetime},
{"unitcost", type number},
{"wiptotal", type number},
{"productName", type text},
{"suffix", Int64.Type},
{"status", type text},
{"closeDate", type datetime},
{"dispositionDate", type datetime}
}
),

#"Added translated column" = Table.AddColumn(
#"Changed Type",
"cause_of_defect(eng)",
each
if [causeOfDefact] <> "" then
let
encodedDescription = Text.Replace(Uri.EscapeDataString([causeOfDefact]), "%20"," "),
apiEndpoint = "https://translate.googleapis.com/",
relativepath = "translate_a/single",
jsonResponse = try
Json.Document(
Text.FromBinary(
Web.Contents(
apiEndpoint,
[
RelativePath = relativepath,
Query = [
client = "gtx",
sl = "th",
tl = "en",
dt = "t",
q = encodedDescription
]
]
)
)
)
otherwise
null,
translatedText =
if jsonResponse
<> null and List.NonNullCount(jsonResponse)
> 0 and List.NonNullCount(List.First(jsonResponse))
> 0
then
try Text.From(List.First(List.First(jsonResponse)){0}) otherwise null
else
null
in
if translatedText <> null then translatedText else ""
else
""

),
#"Renamed Columns" = Table.RenameColumns(
#"Added translated column",
{{"create_date", "Date_Create"}}
)
in
#"Renamed Columns"

From my code, the red part is the part I used to translate, which is using the Google Translate API from My information from Thai to English, which now comes out as a URL code only in Thai. If some of my information is in English It will not send the URL code, but it will be in English as usual. How can I solve this problem?

[Anonymous · Accepted solution]

Hi, @Tiger2514555 

According to your description, you have a problem with encoding and decoding URLs in Power Query, the encoded URLs are not correctly decoded back to normal text after translation, especially for Thai text.

To resolve this issue, you can use the encoding functions in Power Query and the function that decodes the encoded text of the URL. Here is a short action plan to solve your problem:

1. Encode the text of the URL: You have done this part correctly before sending the field to the Google Translate API.

2. Decode the translated text: after receiving the translated text from the API, you should decode it back to normal text. To do this, you can use the function in Power Query (Uri.UnescapeDataString).

Below is the existing code modified to include the decoding step.

#"Added translated column" = Table.AddColumn(#"Changed Type", "cause_of_defect(eng)", each if [causeOfDefact] <> "" then
    let
        encodedDescription = Text.Replace(Uri.EscapeDataString([causeOfDefact]), "%20", " "),
        apiEndpoint = "https://translate.googleapis.com/",
        relativepath = "translate_a/single",
        jsonResponse = try Json.Document(Text.FromBinary(Web.Contents(apiEndpoint, [RelativePath = relativepath, Query = [client = "gtx", sl = "th", tl = "en", dt = "t", q = encodedDescription]]))) otherwise null,
        translatedText = if jsonResponse <> null and List.NonNullCount(jsonResponse) > 0 and List.NonNullCount(List.First(jsonResponse)) > 0 then
            Uri.UnescapeDataString(Text.From(List.First(List.First(jsonResponse)){0})) else null
    else
        null
    in
        if translatedText <> null then translatedText else ""
)

In the modified code snippet above, the key addition is to use of to decode the translated. (text.Uri.UnescapeDataString)

Uri.UnescapeDataString (Note: While there's no direct documentation for , the concept is widely understood and the function name follows the convention used in many programming languages for decoding URL-encoded strings.)

Related link(Uri.EscapeDataString): Uri.EscapeDataString - PowerQuery M | Microsoft Learn

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[wcarter]

There is no "Uri.UnescapeDataString" for PowerQuery M, this solution is not valid for Power BI / Power Query, is there any alternative option?

[amitchandak]

@Tiger2514555 , Please check these URI functions

https://learn.microsoft.com/en-us/powerquery-m/uri-functions