Hello,
Refreshing a dataset in Power BI service that comes from a web content like REST API's has always been a pain in the ass but I've found to do it with using relative path. My example is which I call the REST API of Zendesk to get the groups. The first example works in my Power BI service and it refreshes my dataset.
let
Source = (ClientID as text, ClientSecret as text, LoginName as text, Password as text) =>
let
ZendeskAccessTokenContent = [
grant_type = "password",
client_id = ClientID,
client_secret = ClientSecret,
scope = "read",
username = LoginName,
password = Password
],
ConvertZendeskAccessTokenContent = Text.ToBinary(Uri.BuildQueryString(ZendeskAccessTokenContent)),
ZendeskAccessTokenRequest = Web.Contents("https://blabla.zendesk.com",
[
RelativePath = "/oauth/tokens",
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = ConvertZendeskAccessTokenContent
]
),
ZendeskAccessTokenJSON = Json.Document(ZendeskAccessTokenRequest),
GetZendeskAccessToken = ZendeskAccessTokenJSON[access_token],
ZendeskGroupsRequest = Web.Contents("https://blabla.zendesk.com", [RelativePath = "/api/v2/groups.json", Headers = [#"Authorization"="Bearer " & GetZendeskAccessToken, #"Content-Type"="application/json"]]),
ZendeskGroupsJSON = Json.Document(ZendeskGroupsRequest),
ZendeskGroupsList = @ZendeskGroupsJSON[groups],
ExpandListOfRecords = Table.FromRecords(ZendeskGroupsList),
GetSelectedColumns = Table.SelectColumns(ExpandListOfRecords, {"id", "name", "deleted", "created_at", "updated_at"}),
RenameSelectedColumns = Table.RenameColumns(GetSelectedColumns, {{"id", "ID"}, {"name", "Name of Group"}, {"deleted", "Is Deleted"}, {"created_at", "Created at"}, {"updated_at", "Updated at"}}),
ChangeTypeSelectedColumns = Table.TransformColumnTypes(RenameSelectedColumns, {{"ID", type text}, {"Name of Group", type text}, {"Is Deleted", type logical}, {"Created at", type datetime}, {"Updated at", type datetime}})
in
ChangeTypeSelectedColumns
in
SourceIn my second example I use some sort of iteration to loop over the next pages with a next link value. If I want to use a relative path, this doesn't work. Maybe it has something to do with GetURLParameter.
let
Source = (ClientID as text, ClientSecret as text, LoginName as text, Password as text) =>
let
ZendeskAccessTokenContent = [
grant_type = "password",
client_id = ClientID,
client_secret = ClientSecret,
scope = "read",
username = LoginName,
password = Password
],
ConvertZendeskAccessTokenContent = Text.ToBinary(Uri.BuildQueryString(ZendeskAccessTokenContent)),
ZendeskAccessTokenRequest = Web.Contents("https://blabla.zendesk.com",
[
RelativePath = "/oauth/tokens",
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = ConvertZendeskAccessTokenContent
]
),
ZendeskAccessTokenJSON = Json.Document(ZendeskAccessTokenRequest),
GetZendeskAccessToken = ZendeskAccessTokenJSON[access_token],
GetURLParameter = (URL) =>
let
ZendeskGroupsRequest = Web.Contents(URL, [RelativePath = "/api/v2/groups.json", Headers = [#"Authorization"="Bearer " & GetZendeskAccessToken, #"Content-Type"="application/json"]]),
ZendeskGroupsJSON = Json.Document(ZendeskGroupsRequest),
ZendeskGroupsList = @ZendeskGroupsJSON[groups],
IterationOverNextPage = try @ZendeskGroupsList & @GetURLParameter(ZendeskGroupsJSON[next_page]) otherwise @ZendeskGroupsList
in
IterationOverNextPage,
GetAllZendeskGroups = GetURLParameter("https://blabla.zendesk.com"),
ExpandListOfRecords = Table.FromRecords(GetAllZendeskGroups),
GetSelectedColumns = Table.SelectColumns(ExpandListOfRecords, {"id", "name", "deleted", "created_at", "updated_at"}),
RenameSelectedColumns = Table.RenameColumns(GetSelectedColumns, {{"id", "ID"}, {"name", "Name of Group"}, {"deleted", "Is Deleted"}, {"created_at", "Created at"}, {"updated_at", "Updated at"}}),
ChangeTypeSelectedColumns = Table.TransformColumnTypes(RenameSelectedColumns, {{"ID", type text}, {"Name of Group", type text}, {"Is Deleted", type logical}, {"Created at", type datetime}, {"Updated at", type datetime}})
in
ChangeTypeSelectedColumns
in
SourceCan someone help me with this?
Kind regards
