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:
- Difficult, cumbersome or complex tasks can be packaged into functions. Just fill in the function parameters and you’re done. You don’t have to learn the M-language but just how to use the functions from the custom libraries. (examples: https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-powerquery-in-excel/, https://www.thebiccountant.com/2017/05/23/goal-seeking-and-xirr-in-powerbi-and-powerquery/ )
- Prevention of slow running queries: Many of the solutions that Power Query beginners learn are optimized to be handled by the UI only and not for performance. With custom functions, also beginners can create solutions that handle large amounts of data that won’t bring their computers to a standstill. (examples: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/, https://www.thebiccountant.com/2018/09/30/memory-efficient-clustered-running-total-in-power-bi/)
Thanks for your votes, enjoy & stay queryious 😉
Not convinced this is the function code. Looks like it is related to your last post regarding quickbooQu
sorry, yes – there was a problem with that link!
Thanks for pointing out!
/Imke