Forum Discussion

CrouchingTiger's avatar
CrouchingTiger
Helper I
1 year ago
Solved

web 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...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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

     

  • CrouchingTiger's avatar
    CrouchingTiger
    Helper I
    1 year ago

    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's avatar
      danextian
      Super User
      1 year ago

      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's avatar
        CrouchingTiger
        Helper I
        1 year ago

        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

        --------------------------------------