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

[Anonymous]

Hi @Anonymous 

one last question.

Could you please add if condtion in above M code.

If text.contain(Mycol,"-") then text.afterDelimiter(_,"-") else MyCol

Thanks 

Pravin

[Anonymous]

Hi @Anonymous 

I am able to code it.

Thanks for all your help.

let
GetDataInShape = (myTable as table, myColumn as text) =>
let
ChangeType = Table.TransformColumnTypes(
myTable,
{{myColumn, type text}}),
ExtractTextAfterDelim = Table.TransformColumns(
ChangeType,{{
myColumn,
each if Text.Contains(_,"-") then Text.AfterDelimiter(_, "-") else _,
type text}})
in
ExtractTextAfterDelim
in
GetDataInShape

Thanks,

Pravin

[Anonymous]

Yeah. That should be it. I don't work with M every day so I don't fully remember the nitty-gritty details of the syntax and have to go to the docs.

Glad you did it.

Best
D

[Anonymous]

I can't remember the syntax and don't right now have time to play with this. To know what code you need you have to record this transformation on a dummy table and see in the editor on the right in PQ what has been recorded. Then adjust accordingly and insert into the function. It's just a transformation of a column on a table. Just record it. It works like macros in Excel, doesn't it?

Sorry 😞

Best
D

[Anonymous]

Mate, if you read carefully what the Invoke CF box says, you'll know what you're doing wrong.

Read, mate, read. This is how you learn.

Best
D

[Anonymous]

Mate, create a dummy table with nothing in it (manually) and based on it create the function. Then you'll be able to invoke it on any relevant table.

Easy?

Best
D

[Anonymous]

@Anonymous 

 yes i am trying on simple dummy table only.

Fact

Id      IDNew

ID-1  ID-89

Table

Column1

ID-6.

1)How can i pass ID and IDNew at a time to function?

2)It is showing wrong result if there is no "-" in values.

If "-" is there then it show show values after "-" else value.

Thanks,

Pravin

[Anonymous]

Something like this:

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

Can't remember the exact syntax but... you get the gist. For details, consult the documentation. Such a function can be invoked on any table. But maybe you should also have some error handling in there... just in case... It's up to you what you want to do in there.

Best

D

[amitchandak]

Voted

[ibarrau]

Hi. Sure. You can go to "Edit Queries" or "Transform Data". In that screen you can add columns or do transformations like this one.

Click on the ID column, go to "transform" tab and select Extract->After delimiter. Then write "-" and accept. Then  you have your result.

If you want to learn some M this is the function to do it:

= Table.TransformColumns(#"LastStep", {{"ColumnName", each Text.AfterDelimiter(Text.From(_), "-"), type text}})

Replace LastStep and ColumnName with your data.

Regards, 

[Anonymous]

This functions works perfect !!!!

So the thing is 

let say i have 5 tables all have key1,..key5.

actual requirement is  like i need to create one function that removes "Id -" part. every time i just need to click on invoke function  and select function name and Column name.

How can we do it?

I don't know M code or how to invoke function

Thanks,

Pravin

[ibarrau]

Custom functions are for having in one step multiple transformations. In your case, you want a simple transformation that is in one step. Then it is unnecessary to do it that way. If you use this code in one column it will refresh always for that column. If you need it for another column table you can just add it again.

I guess I'm not sure what you need now. Did my suggestion solve your issue?

Regards,

[Anonymous]

Your function is giving expected output. No issues with it.

I don't want to repeat the same code for another column in another table.

For the same purpose we have custom functions in Power BI. I want to make use of it. Correct me if I am wrong

Table1

Key

Id-4

Id-6

Table2

Key2

Id-100

I am expecting one function which should work for all columns in all tables.

Later let's say i have similar 10 keys in 10 tables

Everytime i just need to do is just click on invoke function and click on function "GetID" and extract all keys by removing "ID-".

---

@ibarrau wrote:

If you need it for another column table you can just add it again.

[ibarrau]

I'm sorry but that is not how power bi works. You do transformations for each table. Each table will run its steps to generate a query for them. You can't create a functions in nowhere that will transform different tables. Custom Functions are not different to any other transformation in the menu, they will be used as another step in the query.

[Anonymous]

Hi @ibarrau 

I hope you understood why i am trying to say.

So we need to write this code for every table?

Can't we make it generalize/dynamic?

Is there any workaround or trick to make it dynamic where user has to just  write generalize function and user Can invoke this function in each query/Table.

thanks for your inputs. Incase there is no workaround i will mark your answer as a solution.

Thanks,

Pravin

[amitchandak]

This is the function ui's which power BI has provided and that how it works as of now, As suggested by members.

You could have used M function

https://docs.microsoft.com/en-us/powerquery-m/text-end

= text.End([Column1],text.Length([Column1])-3)

Of DAX, new column
ID = right(table[Column],len(table[Column])-3)

[Anonymous]

Hi @amitchandak , @ibarrau 

Is there way to do it using invoke function?

Thanks,
Pravin

[amitchandak]

@ImkeF , Please check if you can help.

[Anonymous]

I just wanted to know how this invoke Function works in such cases.

Thanks,

Pravin

[ibarrau]

You have to do transformations table by table. Each table contains their own steps. You can't generate a step that will run in all tables unless you add the step on each table.

Regards,