This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Powe

[Tiger2514555]

I'm using google translate's API to translate my data source, but when I upload it to Power bi Service, it doesn't refresh for me.

My M :
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/translate_a/single?client=gtx&sl=th&tl=en&dt=t&q=" & encodedDescription,
jsonResponse = try Json.Document(Text.FromBinary(Web.Contents(apiEndpoint))) 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"

[tharunkumarRTK]

@Tiger2514555 I made a typo use this 

et
  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_g/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"

[Tiger2514555]

@tharunkumarRTK 
Now there is no error, but now it shows Blank in every row.

[tharunkumarRTK · Accepted solution]

@Tiger2514555 Ok, you might have caught the error already. Please validate and run. I am happy to have an offline discussion

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"

[tharunkumarRTK]

@Tiger2514555 

in a blank query, can you verify if the below code is working or not, in the place of <value> you need to put one value from causeOfDefact column

let

encodedDescription = Text.Replace(Uri.EscapeDataString(<value>), "%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

in

jsonresponse

[Tiger2514555]

@tharunkumarRTK 
My M query now:

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"

[tharunkumarRTK]

@Tiger2514555 you are using dynamic values in the api endpoint. you can make few changes to avoid the problem 

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_g/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"

to know more about dynamic datasources you can checkout this video :  https://youtu.be/sv_upbpq_Cw?si=RV2YyAG2MG9tiIYF

I woud also suggest you to check out this article: https://learn.microsoft.com/en-us/powerquery-m/web-contents

Please dont forget to accept my solution if it helps

Tharun

[Tiger2514555]

@tharunkumarRTK 
So it's error this how can i slove it.