Load Power Query function from GitHub

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
101615_0737_LoadPowerQu1.png


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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s