Skip to content

Instantly share code, notes, and snippets.

@jongio
Last active July 15, 2020 22:17
Show Gist options
  • Save jongio/f8fc841dfcd291a2df1022732095fdd8 to your computer and use it in GitHub Desktop.
Save jongio/f8fc841dfcd291a2df1022732095fdd8 to your computer and use it in GitHub Desktop.
Power BI Query - GitHub REST API
let
BaseUrl = "https://api.github.com/search/issues",
Query = "q=azsdke2e",
Token = "enter your token here",
ItemsPerPage = 100,
Delay = 2,
GetItems = (PageNumber, ItemsPerPage) =>
let Page = "&page=" & Text.From(PageNumber),
PerPage = "&per_page=" & Text.From(ItemsPerPage),
Url = BaseUrl & "?" & Query & Page & PerPage,
Options = [Headers=[ #"Authorization" = "token " & Token ]],
RawData = Function.InvokeAfter( ()=> Web.Contents(Url, Options), #duration(0,0,0, Delay)),
Json = Json.Document(RawData),
Items = Json[#"items"],
TotalCount = Json[#"total_count"],
Result = Record.Combine({[TotalCount = TotalCount, Items=Items]})
in Result,
PageCount = Number.RoundUp(GetItems(1, 1)[TotalCount] / ItemsPerPage),
PageIndices = { 1 .. PageCount },
Pages = List.Transform(PageIndices, each GetItems(_, ItemsPerPage)[Items]),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandData = Table.ExpandRecordColumn(Table, "Column1", {"url", "repository_url", "labels_url", "comments_url", "events_url", "html_url", "id", "node_id", "number", "title", "user", "labels", "state", "locked", "assignee", "assignees", "milestone", "comments", "created_at", "updated_at", "closed_at", "author_association", "active_lock_reason", "draft", "pull_request", "body", "score"}, {"url", "repository_url", "labels_url", "comments_url", "events_url", "html_url", "id", "node_id", "number", "title", "user", "labels", "state", "locked", "assignee", "assignees", "milestone", "comments", "created_at", "updated_at", "closed_at", "author_association", "active_lock_reason", "draft", "pull_request", "body", "score"}),
ExpandUser = Table.ExpandRecordColumn(ExpandData, "user", {"login", "id", "node_id", "avatar_url", "gravatar_id", "url", "html_url", "followers_url", "following_url", "gists_url", "starred_url", "subscriptions_url", "organizations_url", "repos_url", "events_url", "received_events_url", "type", "site_admin"}, {"user.login", "user.id", "user.node_id", "user.avatar_url", "user.gravatar_id", "user.url", "user.html_url", "user.followers_url", "user.following_url", "user.gists_url", "user.starred_url", "user.subscriptions_url", "user.organizations_url", "user.repos_url", "user.events_url", "user.received_events_url", "user.type", "user.site_admin"}),
HtmlUrlDup = Table.DuplicateColumn(ExpandUser, "html_url", "orgrepo"),
HtmlUrlSplit = Table.SplitColumn(HtmlUrlDup, "orgrepo", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"orgrepo.1", "orgrepo.2", "orgrepo.3", "orgrepo.org", "orgrepo.repo", "item.type", "orgrepo.7"}),
OrgRepoChangeType = Table.TransformColumnTypes(HtmlUrlSplit,{{"orgrepo.1", type text}, {"orgrepo.2", type text}, {"orgrepo.3", type text}, {"orgrepo.org", type text}, {"orgrepo.repo", type text}, {"item.type", type text}, {"orgrepo.7", Int64.Type}}),
DateFieldsChangeType = Table.TransformColumnTypes(OrgRepoChangeType,{{"created_at", type datetime}, {"updated_at", type datetime}}),
OrgRepoDup = Table.DuplicateColumn(DateFieldsChangeType, "orgrepo.org", "orgrepo.org.copy"),
OrgSlashRepoDup = Table.DuplicateColumn(OrgRepoDup, "orgrepo.repo", "orgrepo.repo.copy"),
OrgSlashRepoRename = Table.RenameColumns(OrgSlashRepoDup,{{"orgrepo.org.copy", "orgrepo.org2"}, {"orgrepo.repo.copy", "orgrepo.repo2"}}),
OrgSlashRepoMerge = Table.CombineColumns(OrgSlashRepoRename,{"orgrepo.org2", "orgrepo.repo2"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"orgrepo.orgrepo"),
CleanUp = Table.RemoveColumns(OrgSlashRepoMerge,{"orgrepo.1", "orgrepo.2", "orgrepo.3", "orgrepo.7"})
in
CleanUp
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment