Custom Function in Query

[Anonymous]

I Can do this task using modelling and DAX.

But i want it using "invoke Custom Functions".

Requirement is i have key column as below

Key

Id-1

Id-10

Id-100

So i want to display only number and remove "ID-" from column

Thanks,

Pravin

[v-lionel-msft]

Hi @Anonymous ,

Even if you use "invoke Custom Functions", still create a function for each table, Unless you merge all your tables into one table.

You can refer to the code:

let
    Source = () => let
        Source = Excel.Workbook(File.Contents("C:\Users\xxx\Desktop\x4.xlsx"), null, true),
        Sheet6_Sheet = Source{[Item="Sheet6",Kind="Sheet"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Sheet6_Sheet,{{"Column1", type text}}),
        #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.AfterDelimiter(_, "-"), type text}})
    in
       #"Extracted Text After Delimiter"
in
    Source

So, the most effective way is to refer to @ibarrau 's answer.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-lionel-msft , @ibarrau , @amitchandak 

Thanks For your solutions.

I don't know this functionality is there or not. If not then microsoft need to add some functionality like this which is mostly available in programing languages. we just need to create one funtion for all to avoid many steps in each table.

Let say, i have done some transformation like removing delimeters then taking right 2 character from text and then again i have done some 2 3 steps on this.

We have 4 5 tables and each table we have same requirement. So its better to have one function for all and you need to just call function with parameter to avoild repeating steps in each query.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

If you have several tables with the same structure, you can always create a UNION of the tables, apply all the transformations you want to the big table and then split the table into its constituents again. This is how you reuse logic.

Best
D

[Anonymous]

Hi @Anonymous 

Table structure is not same.

Actully the scenario is we have one fact table and 5 dimension tables and each dimension table have "-"number key.

But fact have only key as numbers and we need to remove delimiter("-") from keys of all dimensions and then join with fact table.

I have proposed 2 solutions to client one using dax and one using M code which is given by @ibarrau . But they are expecting one function for all.

Is it possible to have 1 function for all table like stored prcoedures or any programming language function?

I have posted idea here

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/39789040-one-common-function-for-all-tables

If you think this needs to be added in Power BI. Please vote for idea. @Anonymous @amitchandak @ibarrau @v-lionel-msft 

Thanks,

Pravin

[Anonymous]

The only thing you can have is a general M function that will be invoked for each of the tables when they get transformed by Power Query. No other way.

If the tables are SQL tables, it's easy to add calculated columns to them with the right transformation and hence there'll be no need to think about it anymore.

Best
D

[Anonymous]

Hi @Anonymous 

It's not about SQL tables.

What if we have different sources like CSV, text files,Excel files.

There is no work around for such issues. Everytime we need to add all the steps in each and every query/Table.

It's better to have one function for all so that it will reduce time efforts required for data preparation.

Hence i have posted it on power bI ideas.

Thanks,

Pravin

[Anonymous]

Bear in mind, though, that if your data sources are of different nature, then you have to out of necessity write transformation logic for each and every data source. There is no silver bullet and no technology can be data-source agnostic. Even in OOP there is a pattern known as Adapter that lets you unify different interfaces into one but YOU have to write logic for each and every different interface in the adapter.

Sorry, you're a bit out of luck here 😞

Best
D

[Anonymous]

Hi @Anonymous 

I think you are not getting my point.

I am expecting simple functionality like this

All i need to call function getid(tab1,key) in first dimension

getid(tab2,key) in second dimension

I need to define function somewhere like this

Getid(Table_name,Column_name)

Begin

var Step1=Mid(Table_name[Column_name],find(Table_name[Column_name],"-"),len(Table_name[Column_name]))

var Step2=Right(Step1,2)

return 

Step2

End

Don't mind for syntax. 

I am just trying to explain you what kind of functionality we should have.

Thanks,

Pravin

[Anonymous]

Well, I think such a function that takes a table and a column name and returns a table is perfectly doable in M anyway :)))

Go to the documentation about M and read...

Best
D

[Anonymous]

Hi @Anonymous 

No it should have rerurn column in calling table.

Could you please suggest which documentation i need to check.

I am new to M code.

Thanks,

Pravin

[Anonymous]

A hint is this: record the steps you want on one table and then turn the code into a function that will take a table and all other params you need as input and return a table with the transformations applied.

I think this is the reusability you want...

Best
D

[Anonymous]

An M-function can do ANYTHING. It can return a scalar, a table, a record, a list... you name it. And it can take as inputs anything. There you have it - your needed functionality.

The documentation (of which I have once read all) is here: https://docs.microsoft.com/en-us/powerquery-m/

Best
D

[Anonymous]

Hi @Anonymous 

I mean

If i call function get id from table1 it should add one calculated column in tab1.

if i call function get id from table2 it should add 1 calculated column in tab2.

As per other super users(In same thread ) it's not possible to have single function for all tables. we need to do it seperately in each table.

I want reusability of below code.

in below code how could you make it dynamic.

Column1 and sheet6 are hardcoded here

let
    Source = () => let
        Source = Excel.Workbook(File.Contents("C:\Users\xxx\Desktop\x4.xlsx"), null, true),
        Sheet6_Sheet = Source{[Item="Sheet6",Kind="Sheet"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Sheet6_Sheet,{{"Column1", type text}}),
        #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.AfterDelimiter(_, "-"), type text}})
    in
       #"Extracted Text After Delimiter"
in
    Source

Thanks,

Pravin

[Anonymous]

You certainly can create a function as well that will iterate all your tables in the model and apply the function above to them.

Best
D

[Anonymous]

Hi @Anonymous ,

= let GetDataInShape= (myTable as table, myColumn as text) =>
let ChangeType = Table.TransformColumnTypes(myTable,{{myColumn,text}}),
ExtreactAfterDelimiter=Table.TransformColumns(changeType,{{
mycolumn,each Text.AfterDelimiter(_,"-"),text}})
in ExtreactAfterDelimiter
in GetDataInShape

This code is throwing error.

"Expression.Error: The name 'text' wasn't recognized. Make sure it's spelled correctly."

Thanks,

Pravin

[Anonymous · Accepted solution]

It works for me. You have to create a function in Power Query and then change its body accordingly.

Best

D

[Anonymous]

@Anonymous 

This is what i am expecting.

Perfect!!!!!!!!!!

Can we modify it something like it should create/modify column in same query?

Now whenever i am specifying table name and column name it is creating new table with name "Invoke function".

I am asking this because let's say for fact table we have 6 key columns and we need to remove "ID -" from three or four columns.

It's not good choice to create 4 seperate "Invoke function","Invoke function 1","Invoke function 2"...so on to modify single fact table.

Is it possible?

But really appriciate your efforts. This solution is really amazing. 

Thanks,

Pravin

[Anonymous]

Instead of passing in a piece of text as the col name, you can pass a list of names. Inside a function you can do ANYTHING.

Best
D

[Anonymous]

Hi @Anonymous 

I don't know how to pass list of columns. Could you please suggest how to pass it.

I never worked on M code. I only worked on DAX.

One thing i have observed

I have created fact table and dimension table.

the function which you have suggested.

When I am clicking on function it is asking Mytable and column name and also for Mytable it has given dropdown arrow which is providing all tables list. This is working as expected.

When I click on particular table/query then click on "invoke custom function" it is asking for  "Functionquery" and there i have selected create function that is GETID. and then it is again asking for two parameters table name and column name but in table name it is not allowing me to select same table instead of that it is showing other tables in dropdown.

I think it is because we are trying to create column here but our fucntion is creating new table. AM i Right?

Thanks,

Pravin

[Anonymous]

One last thing.... A list of columns can be created from a table's column that contains the columns or it can be created inline, manually as a list literal. For details, consult the M docs.

Best
D