Why scenario of “Loading Power Query function from GitHub” is interesting?
Assume that you work in big company, or small, doesn’t matter.
You and your colleagues use shared library of Power Query functions, published on shared drive.
You also have separated workstation or server, that has no access to the internet, only can access shared folders in corporate network. This workstation updating workbooks with Power Query all day and night long.
Typical model
From time to time, you also have to collect data from web using PQ functions. E.g. if you are using cloud ERP, like SAP Business ByDesign.
When you are in the office, everything is OK, your laptop is in corporate network, you simply take functions from shared folder.
Of course, company’s network resources are not accessible from the outside – from home, from partner’s office or other place.
You probably can use VPN, it solves problem, however, partially. Because VPN reduces speed to some sites.
What would be good in such situation
- library on the web that can be used by you and colleagues from everywhere
- library on corporate network for isolated server
If you have such two libraries then following scenario becomes possible :
When you or your colleague in the office – you simply take function from corporate network
When you outside of company network, you load same function from web library
This simple function, Load.m published to GitHub, will help you
https://github.com/IvanBond/pquery/blob/master/Load.m
(fnName as text, optional BasePath as text) as function =>
let
//If you wish to hardcode the path to load the queries from, you can edit the following line:
DefaultPath = “C:\PQuery\”,
GitHubPath = “https://raw.githubusercontent.com/IvanBond/pquery/master/“,
BasePath = if (BasePath <> null) then BasePath else DefaultPath,
Path = BasePath & (if Text.End(BasePath, 1) <> “\” then “\” else “”),
File = Path & fnName & “.m”,
Function = try Expression.Evaluate(Text.FromBinary(Binary.Buffer(File.Contents(File))), #shared)
otherwise Expression.Evaluate( Text.FromBinary(Binary.Buffer(Web.Contents(GitHubPath & fnName & “.m”))), #shared)
in
Function
If you provide BasePath as parameter – function will take code from there.
If BasePath is empty then Load.m will use hardcoded DefaultPath.
If Load.m failed to load function from BasePath and DefaultPath – then it goes to hardcoded GitHubPath.
GitHub library opens wide opportunities, because it is shared, open-source, accessible from any place.