Web.Connect

[zibster]

HI,

Would anyone know how to make the refresh work in Power BI app for the code below? It works perfect in desktop App.

Thanks Z

let
token = "-------------------------------------",

    baseUrl = "https://www.ncdc.noaa.gov/cdo-web/api/v2/",

    endpoint = "data",

    stationid1 = ":US1COLP0022",

    datasetid = "GHCND",

    StartDate = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-366),"YYYY-MM-DD"),

    EndDate = Date.ToText(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-360),"YYYY-MM-DD"),

    url = Text.Combine({baseUrl,endpoint,"?stationid=",datasetid,stationid1,"&datasetid=",datasetid,"&startdate=",StartDate,"&enddate=",EndDate}),

    limit = 1000, // Defaults to 25, maximum is 1000.

    request = Web.Contents(

        url,

        [Headers = [token = token],

            Query = [

                limit = Text.From(limit)

            ]

        ]

    ),

    requestData = Json.Document(request),

    metadata = requestData[metadata],

    results = requestData[results],

    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"date", "datatype", "station", "attributes", "value"}, {"Column1.date", "Column1.datatype", "Column1.station", "Column1.attributes", "Column1.value"})

in

    #"Expanded Column1"

[v-lili6-msft]

hi @zibster 

After you publish the report to Power BI service, please go to the Dataset Settings page. If the Power Query to get data contains web.page function, you will need a gateway for the dataset. You can configure a on-premise data gateway and create a web data source with the same URL used in desktop. Then set the dataset use this data gateway. 

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

Regards,

Lin

[zibster]

Hi,

Unfortunately my Co. does not allow personal gateway, i was hoping for some other solution.

Thanks