Forum Discussion
Helper Iweb data Json conversion error
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
Helper IHi, 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?
Super UserPower 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
- CrouchingTiger
Thank you for response
What I'm trying to do is get data from web API and refresh it automatically using only Power BI.
But according to your text, down load as a josn file every time.
below code is worked(Get data) but missing refresh in Power BI settings.----------------
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 &
----------------
But, If I set a code as below, It's can't get data from URL with error message.
It's same whether RelativePath="getVilageFcst?" or RelativePath="getVilageFcst" not.
And it's the same even if I enter the query value directly.----------------
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]])),----------------
Error message.----------------------
DataFormat.Error: We found an unexpected character in the JSON input.
Details:
Value=<Position=0
--------------------------------------
- danextian
This is a sample approach to address your issue. Downloading a local copy minimizes repeated API calls, making troubleshooting faster and more efficient. Once you identify the characters that Power BI cannot handle and remove them in subsequent steps, you can then update your code to include the removal of those invalid characters.
- CrouchingTiger
Thank you for your guide. I'll try it
And could you please let me know, how can I get the full string of Web.Contents("full string") in it
I think the full string shoud be as below