Forum Discussion
Helper Iweb data Json conversion error
Hi, experts
What I want to do is schedule refresh of web Open API in Power BI.
So, I refered many of articles to fix it. using relativepath and query.
Code as below
(NumOfRows as text, PageNo as text, DataType as text, NX as text, NY as text ) =>
let
CurrentDateTime = DateTime.LocalNow(),
CurrentHour = Time.Hour(Time.From(CurrentDateTime)),
AdjustedHour = if CurrentHour < 2 then 23 else Number.RoundDown((CurrentHour - 2) / 3) * 3 + 2, // It is calculated in units of 3 hours, but if it is 0 o'clock, it is set to 23 o'clock the previous day.
RecentBaseTime = Text.PadStart(Text.From(AdjustedHour), 2, "0") & "00", // Final 4 character, HHMM
Today = Date.ToText(Date.From(DateTime.LocalNow()), "yyyyMMdd"), // Create today's date dynamically
Source =
Json.Document(Web.Contents("https://apis.data.go.kr/1360000/VilageFcstInfoService_2.0/",
[RelativePath="getVilageFcst?",
Query= [
serviceKey=APIKey,
numOfRows=NumOfRows,
pageNo=PageNo,
dataType=DataType,
base_date=Today,
base_time=RecentBaseTime,
nx=NX,
ny=NY]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded response" = Table.ExpandRecordColumn(#"Converted to Table", "response", {"header", "body"}, {"response.header", "response.body"}),
#"Expanded response.header" = Table.ExpandRecordColumn(#"Expanded response", "response.header", {"resultCode", "resultMsg"}, {"response.header.resultCode", "response.header.resultMsg"}),
#"Expanded response.body" = Table.ExpandRecordColumn(#"Expanded response.header", "response.body", {"dataType", "items", "pageNo", "numOfRows", "totalCount"}, {"response.body.dataType", "response.body.items", "response.body.pageNo", "response.body.numOfRows", "response.body.totalCount"}),
#"Expanded response.body.items" = Table.ExpandRecordColumn(#"Expanded response.body", "response.body.items", {"item"}, {"response.body.items.item"}),
#"Expanded response.body.items.item" = Table.ExpandListColumn(#"Expanded response.body.items", "response.body.items.item"),
#"Expanded response.body.items.item1" = Table.ExpandRecordColumn(#"Expanded response.body.items.item", "response.body.items.item", {"baseDate", "baseTime", "category", "fcstDate", "fcstTime", "fcstValue", "nx", "ny"}, {"response.body.items.item.baseDate", "response.body.items.item.baseTime", "response.body.items.item.category", "response.body.items.item.fcstDate", "response.body.items.item.fcstTime", "response.body.items.item.fcstValue", "response.body.items.item.nx", "response.body.items.item.ny"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded response.body.items.item1",{{"response.header.resultCode", Int64.Type}, {"response.header.resultMsg", type text}, {"response.body.dataType", type text}, {"response.body.items.item.baseDate", Int64.Type}, {"response.body.items.item.baseTime", Int64.Type}, {"response.body.items.item.category", type text}, {"response.body.items.item.fcstDate", Int64.Type}, {"response.body.items.item.fcstTime", Int64.Type}, {"response.body.items.item.fcstValue", type text}, {"response.body.items.item.nx", Int64.Type}, {"response.body.items.item.ny", Int64.Type}, {"response.body.pageNo", Int64.Type}, {"response.body.numOfRows", Int64.Type}, {"response.body.totalCount", Int64.Type}})
in
#"Changed Type"
If I do this then, an error occur.
If I tested the Source (URL) is fine when I put in it to web browser address
Please help me about this. It's really close to make it...
First code is below, without relativepath and query. then it works but can't do scheduled refresh.
let
CurrentDateTime = DateTime.LocalNow(),
CurrentHour = Time.Hour(Time.From(CurrentDateTime)),
AdjustedHour = if CurrentHour < 2 then 23 else Number.RoundDown((CurrentHour - 2) / 3) * 3 + 2,
RecentBaseTime = Text.PadStart(Text.From(AdjustedHour), 2, "0") & "00",
Today = Date.ToText(Date.From(DateTime.LocalNow()), "yyyyMMdd"),
Source = Json.Document(Web.Contents("https://apis.data.go.kr/1360000/VilageFcstInfoService_2.0/getVilageFcst" & "?serviceKey=API Key" & "&numOfRows=12" & "&pageNo=1" & "&dataType=JSON" & "&base_date="&Today & "&base_time="&RecentBaseTime & "&nx=61" & "&ny=110")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded response" = Table.ExpandRecordColumn(#"Converted to Table", "response", {"header", "body"}, {"response.header", "response.body"}),
#"Expanded response.header" = Table.ExpandRecordColumn(#"Expanded response", "response.header", {"resultCode", "resultMsg"}, {"response.header.resultCode", "response.header.resultMsg"}),
#"Expanded response.body" = Table.ExpandRecordColumn(#"Expanded response.header", "response.body", {"dataType", "items", "pageNo", "numOfRows", "totalCount"}, {"response.body.dataType", "response.body.items", "response.body.pageNo", "response.body.numOfRows", "response.body.totalCount"}),
#"Expanded response.body.items" = Table.ExpandRecordColumn(#"Expanded response.body", "response.body.items", {"item"}, {"response.body.items.item"}),
#"Expanded response.body.items.item" = Table.ExpandListColumn(#"Expanded response.body.items", "response.body.items.item"),
#"Expanded response.body.items.item1" = Table.ExpandRecordColumn(#"Expanded response.body.items.item", "response.body.items.item", {"baseDate", "baseTime", "category", "fcstDate", "fcstTime", "fcstValue", "nx", "ny"}, {"response.body.items.item.baseDate", "response.body.items.item.baseTime", "response.body.items.item.category", "response.body.items.item.fcstDate", "response.body.items.item.fcstTime", "response.body.items.item.fcstValue", "response.body.items.item.nx", "response.body.items.item.ny"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded response.body.items.item1",{{"response.header.resultCode", Int64.Type}, {"response.header.resultMsg", type text}, {"response.body.dataType", type text}, {"response.body.items.item.baseDate", Int64.Type}, {"response.body.items.item.baseTime", Int64.Type}, {"response.body.items.item.category", type text}, {"response.body.items.item.fcstDate", Int64.Type}, {"response.body.items.item.fcstTime", Int64.Type}, {"response.body.items.item.fcstValue", type text}, {"response.body.items.item.nx", Int64.Type}, {"response.body.items.item.ny", Int64.Type}, {"response.body.pageNo", Int64.Type}, {"response.body.numOfRows", Int64.Type}, {"response.body.totalCount", Int64.Type}})
in
#"Changed Type"
Try these:
let // Parameters (can be replaced with dynamic inputs or function arguments) APIKey = "YourAPIKey", NumOfRows = "12", PageNo = "1", DataType = "JSON", Today = "20241204", // Example date; replace with dynamic logic if needed RecentBaseTime = "0800", // Example time; replace with dynamic logic if needed NX = "61", NY = "110", // Query Parameters as a Record QueryParameters = [ serviceKey = APIKey, numOfRows = NumOfRows, pageNo = PageNo, dataType = DataType, base_date = Today, base_time = RecentBaseTime, nx = NX, ny = NY ], // Build Query String QueryString = Uri.BuildQueryString(QueryParameters), // API Request Source = Json.Document(Web.Contents( "https://apis.data.go.kr/1360000/VilageFcstInfoService_2.0/getVilageFcst?", [Query = QueryParameters] )) in SourceQueryString = Uri.BuildQueryString(QueryParameters) will return serviceKey=YourAPIKey&numOfRows=12&pageNo=1&dataType=JSON&base_date=20241204&base_time=0800&nx=61&ny=110
let // Parameters (can be replaced with dynamic inputs or function arguments) APIKey = "YourAPIKey", NumOfRows = "12", PageNo = "1", DataType = "JSON", Today = "20241204", // Example date; replace with dynamic logic if needed RecentBaseTime = "0800", // Example time; replace with dynamic logic if needed NX = "61", NY = "110", // API Request Source = Json.Document(Web.Contents( "https://apis.data.go.kr/1360000/VilageFcstInfoService_2.0/", [ RelativePath = "getVilageFcst?", Query = [ serviceKey = APIKey, numOfRows = NumOfRows, pageNo = PageNo, dataType = DataType, base_date = Today, base_time = RecentBaseTime, nx = NX, ny = NY ] ] )) in Source
10 Replies
- danextian
Super User
There's possibly an invalid character in the JSON input that M cannot handle. Please see this thread https://community.fabric.microsoft.com/t5/Desktop/quot-We-found-an-unexpected-character-in-the-JSON-input-quot/td-p/3724488
CrouchingTigerHi, danextian , Thank you for reply
I'm not sure. Itt's related to my question.
The Query method is not working? with JSON document? So, it can't be handle in Power BI for refresh?- danextian
Power BI can handle JSON but if there may be characters in the JSON file that Power BI can't handle. You need to figure out what those are move them either from the source or in Power Query. Below is a sample query that replaces \31 with nothing. Attached is the sample json file.
let // Load the file as binary Source = File.Contents("C:\Users\username\folder\sample json.json"), // Convert binary to text FileAsText = Text.FromBinary(Source, TextEncoding.Utf8), // Replace invalid characters (e.g., "\31") with a valid placeholder CleanedText = Text.Replace(FileAsText, "\31", ""), // Optional: Validate the JSON after cleaning ParsedJson = Json.Document(CleanedText) in ParsedJson
- cpwebb
Microsoft Employee
It may or may not solve the problem you're having here, but setting the Skip Test Connection property on your credentials in the Service might help: https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/
- CrouchingTiger
Hi cpwebb thank you for share the post.
I already saw it and applied the "skip test connection" option in PBI service. But it's not worked