cancel
Showing results for 
Search instead for 
Did you mean: 

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

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).

Post image
= 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...

 

Comments

Awesome!!

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])

 

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,