Writing data to GitHub using Power Query only

You shouldn’t do it …

Generally it’s a very bad idea to execute commands in Power Query that write out data to a source, as these commands might be executed multiple times (https://blog.crossjoin.co.uk/2018/10/07/power-query-write-data/)

… unless … maybe ?

However, as with most good rules, there are exceptions. I leave it to you to decide whether my use case here is a valid candidate for it. It doesn’t execute the code twice, because I execute the query only from the query editor and none of the other queries is referencing its results. But please see for yourself – Writing data to GitHub using just Power Query:

The video

In the video I show how I enrich my M-functions with metadata before loading it directly with Power Query into a new Gist on GitHub. Then I trigger an automatic update of my Function-library (M-extension). Therefore I have to switch to Power BI, because it currently not possible to run R- or Python-Scripts in Excel (which writes the .mez-file for me into the destination-folder).

Trading code for votes

The code I’m sharing today is the one that exports the M-code to GitHub. I’m going to share the full solution, as soon as the following features are implemented in Excel (like they are in Power Query for Power BI currently):

You can help this by upvoting the ideas of the links above. Actually, my guess is that we need around 1000 votes for these features to be considered. So please share this article with your colleagues and friends to make this happen.

The code

let func =
(Description as text, FunctionName as text, Content as text, AccessToken as text, optional public as any) =>
let
Public = if public = null then false else true,
GitRecord = [description=Description, public=Public, files=Record.FromTable(#table({"Name", "Value"}, {{FunctionName, [content=Content]}}))],
URL = "https://api.github.com/gists?access_token=" & AccessToken,
BinaryJson = Json.FromValue(GitRecord),
Web= Json.Document(Web.Contents(URL, [#"Headers"=[#"Content-type"="application/json", access_token= AccessToken], Content=BinaryJson]))
in
Web ,
documentation = [
Documentation.Name = " Export.CreateGist.pq ",
Documentation.Description = " Creates a secret gist with the parameters provided. Optional parameter to make it public. ",
Documentation.LongDescription = " Creates a secret gist with the parameters provided. Optional parameter to make it public. ",
Documentation.Category = " Other ",
Documentation.Source = " Imke Feldmann: www.TheBIccountant.com. ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com. For details see: https://www.thebiccountant.com/2019/01/13/writing-data-to-github-using-power-query-only/ . ",
Documentation.Examples = {[Description = " For details see: https://www.thebiccountant.com/2019/01/13/writing-data-to-github-using-power-query-only/. ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

You need an access token from GitHub for Power Query to pull your data from your repos and gists: https://github.com/settings/tokens

Why I am so passionate about this?

In my eyes, these features hold the key to make the Power Tools in Excel really easy and efficient to use:

Thanks for your votes, enjoy & stay queryious 😉

Comments (2) Write a comment

  1. Not convinced this is the function code. Looks like it is related to your last post regarding quickbooQu

    Reply

Leave a Reply