Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
It works for me. You have to create a function in Power Query and then change its body accordingly.
Best
D
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.
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.
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
If you think this needs to be added in Power BI. Please vote for idea. @Anonymous @amitchandak @ibarrau @v-lionel-msft
Thanks,
Pravin
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
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
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
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
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
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
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
It works for me. You have to create a function in Power Query and then change its body accordingly.
Best
D
@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
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |