[PowerQuery] Call custom functions file from Github or local file

[ibarrau]

Hi! Today I'm going to show you how to call a power query custom function from file in edit queries. You can now create a repository of custom functions and reuse them from there. Maybe in the near future we can have our m code more organized in our organization. This post will help us get throw this step by step.

First of all we are going to open a notepad and paste our function like the following example format:

let
    TextAsNumber = (valor as number)  => 
        let
            Mostra = "The number is: " & Text.From(valor)
        in
    Mostra
in
    TextAsNumber

Save file as a ".pq" file to your repository. In this case I'm going to use my github.

Note: pq extension is just if you want to work with it using power query sdk in visual studio.

Then on Power Bi Desktop Edit Queries just get data from Blank Query and paste the code below replacing the string with raw path from your github, repo or local path (in this last case you should change the word Web to File for contests).

= Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/ibarrau/PowerBi-code/master/PowerQuery/PQFun.pq")),#shared)

That code will create for us a function in edit query from a repository. It runs a function from text validating it with #shared (a datasource on power bi with actual functions)

That's all folks, now you have a custom fuction in power bi from ".pq" file.

Hope this helps to manage a good repository of custom functions. Not only for having a better administration but also for reusing useful functions on different power bi files.

P/D Schedule Refresh with Expression.Evaluate doesn't work on service. We can now vote for this hoping one day would be available:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/18352027-support-refresh-from-the...

[MartinZurita]

Awesome!!

[FrankTonsen]

Expression.Evaluate does work in the service. However, #shared is desktop only.

Github:

Date.Today.pq = () => Date.From(DateTime.FixedLocalNow())

Both, Power BI Desktop and Power BI Service:

Expression.Evaluate(Text.FromBinary(Web.Contents("https://raw.githubusercontent.com/ibarrau/PowerBi-code/master/PowerQuery/Date.Today.pq")),[Date.From=Date.From, DateTime.FixedLocalNow=DateTime.FixedLocalNow])

[ibarrau]

Thanks for the detail reply @FrankTonsen . However, even if Expression.Evaluate work addinag functions like that, the idea of using expression with #shared to avoid specifing this won't work.

The post is suggesting a repository where you can have a big power query file with transformation and people working together with the possibility to merge if there are conflicts. This kind of file can't be specified in Expression.Evaluate.

Regards,

[drorli]

@ibarrau , this is pretty cool but still not fully useful.

Is there a way to have a .pq file with multiple functions in it (like an external library)?

Do you know if there is a plan to enable users to add custom "libraries" of function which can be imported into #shared and then used like native functions?

That would be very powerful (I have a sack of ideas for useful functions after using this language for a pretty short time ...).

[ibarrau]

@drorli. Sadly this is just a reasearch. You can't use it in a production environment because it won't work in Power Bi Service. You can't read from a .pq file and I couldn't make a Power Bi team to see the important deal this can be.

The only way to make kind of libraries it's creating Custom Connectors. However you have limitations on the connections to data and functions you can use :S

It's a sad story