How to get table directly from GitHUB using web connector and outh2?

[jaryszek]

Hi Guys,

I created ustom connector like here:

[DataSource.Kind="GitHub", Publish="GitHub.Publish"]
shared GitHub.Contents = (repositoryName as text, optional folderPath as text) =>
    let
        baseUrl = "https://api.github.com/",
        url = "repos/xxx/" & repositoryName & "/contents/" & folderPath,
        source = Json.Document(Web.Contents(baseUrl, [
            RelativePath = url,
            Headers = [
                #"Authorization" = "Bearer " & Extension.CurrentCredential()[access_token],
                #"User-Agent" = "PowerQuery"
            ]
        ]))
    in
        source;

GitHub = [
    Authentication = [
        OAuth = [
            StartLogin = (resourceUrl, state, display) =>
                let
                    authorizeUrl = "https://github.com/login/oauth/authorize?" &
                        "client_id=" & client_id &
                        "&redirect_uri=https://oauth.powerbi.com/views/oauthredirect.html" &
                        "&scope=repo" &
                        "&state=" & state
                in
                    [
                        LoginUri = authorizeUrl,
                        CallbackUri = "https://oauth.powerbi.com/views/oauthredirect.html",
                        WindowHeight = 720,
                        WindowWidth = 1024,
                        Context = null
                    ],

            FinishLogin = (context, callbackUri, state) =>
                let
                    parts = Uri.Parts(callbackUri)[Query],
                    code = parts[code],
                    access_token_response = Json.Document(Web.Contents("https://github.com/login/oauth/access_token", [
                        Content = Text.ToBinary("client_id=" & client_id & "&client_secret=" & client_secret & "&code=" & code),
                        Headers = [
                            #"Content-Type" = "application/x-www-form-urlencoded",
                            Accept = "application/json"
                        ]
                    ])),
                    access_token = access_token_response[access_token]
                in
                    [
                        access_token = access_token
                    ],

            Refresh = (resourceUrl, refresh_token) => error "GitHub does not support refresh tokens.",
            TestConnection = (access_token_record) => {"https://api.github.com/user"},
            AccessToken = (access_token_record) => "Bearer " & access_token_record[access_token]
        ]
    ]
];

GitHub.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = GitHub.Icons,
    SourceTypeImage = GitHub.Icons
];

GitHub.Icons = [
    Icon16 = { Extension.Contents("GitHub16.png"), Extension.Contents("GitHub20.png"), Extension.Contents("GitHub24.png"), Extension.Contents("GitHub32.png") },
    Icon32 = { Extension.Contents("GitHub32.png"), Extension.Contents("GitHub40.png"), Extension.Contents("GitHub48.png"), Extension.Contents("GitHub64.png") }
];

 But i want to add function to get also directly table in csv within the github folder.
I saw that in a Microsoft sample there is code:

[DataSource.Kind="GithubSample"]
shared GithubSample.PagedTable = Value.ReplaceType(Github.PagedTable, type function (url as Uri.Type) as nullable table);

Source: https://learn.microsoft.com/en-us/power-query/samples/github/readme 

but i do not know how to implement it.

Can anybody help? 
Best,
Jacek

[jaryszek]

thank you!!!

[johnbasha33 · Accepted solution]

Hi @jaryszek 
You're doing great so far — building your custom GitHub connector is not easy stuff.
I'll help you extend it nicely so you can also fetch CSVs directly from a GitHub repo and load them as a table.

What you want:

Add a function to your connector that:

• Downloads a .csv file from GitHub

• Reads it into a Power Query table

Exactly like GithubSample.PagedTable in the Microsoft example.

Here's how you can extend your connector:

You add a new function like this:

[DataSource.Kind="GitHub", Publish="GitHub.Publish"]
shared GitHub.LoadCsvTable = (repositoryName as text, filePath as text) =>
let
baseUrl = "https://raw.githubusercontent.com/xxx/" & repositoryName & "/main/" & filePath,
csvContent = Web.Contents(baseUrl, [
Headers = [
#"Authorization" = "Bearer " & Extension.CurrentCredential()[access_token],
#"User-Agent" = "PowerQuery"
]
]),
csvTable = Csv.Document(csvContent, [
Delimiter = ",",
Columns = 10, // You can change based on your file
Encoding = 65001, // UTF-8
QuoteStyle = QuoteStyle.Csv
])
in
csvTable;
shared GitHub.LoadCsvTable = (repositoryName as text, filePath as text) =>
let
baseUrl = "https://raw.githubusercontent.com/xxx/" & repositoryName & "/main/" & filePath,
csvContent = Web.Contents(baseUrl, [
Headers = [
#"Authorization" = "Bearer " & Extension.CurrentCredential()[access_token],
#"User-Agent" = "PowerQuery"
]
]),
csvTable = Csv.Document(csvContent, [
Delimiter = ",",
Encoding = 65001,
QuoteStyle = QuoteStyle.Csv
]),
output = Table.PromoteHeaders(csvTable)
in
output;

Then inside Power BI or Power Query UI:

You can call:
GitHub.LoadCsvTable("your-repo-name", "folder/subfolder/yourfile.csv")

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!