web data Json conversion error

[CrouchingTiger]

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"

[cpwebb]

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

[danextian]

Hi @CrouchingTiger 

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-... 

[CrouchingTiger]

Hi, @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

[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]

Hi, @danextian Thank you for your guide.

I changed APIKey to decoding and It worked.

Means, same code works, just change APIKey decoding.

However, another problem arose in refresh history.

Could you please guide me about this?

error details : Data source error: DataSource.Error: The request was aborted: Could not create SSL/TLS secure channel.. https://apis.data.go.kr/1360000/VilageFcstInfoService_2.0/. . The exception was raised by the IDbCommand interface. Table: Invoked Function.

same code-------------------------------------------------

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]])),

[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

https://apis.data.go.kr/1360000/VilageFcstInfoService_2.0/getVilageFcst?serviceKey=APIKey&numOfRows=...

[danextian · Accepted solution]

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
    Source

 QueryString = 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