Forum Discussion
Custom Function, How to Deal With New Column Name and Previous Step
Hi experts,
I would like to convert the following step into a custom function for future use in a new step or in a new query. However, I got stuck about the definition of current column and the previous step, e.g. how can I input a text to replace the variable columns and table/previous step?
I guess it will be a little bit different for a new step or a new query. As you can see that the newly created column "Col1" is used in different places, sometimes the format is [], sometimes the format is {""}, I do not know how to deal with the difference when convert the following formual into a function.
Variable columns are "Col1", "Col2", other columns will not be changed no matter in new query or step.
Table.ReplaceValue(
Table.FillUp(
Table.AddColumn(
#"Previous Step",
"Col1",
each if [Col2] <> null then [Col3] else null
),
{"Col1"}
),
each [Col1],
each if
List.NonNullCount(
Table.SelectRows(
#"Previous Step",
(x) => x[Index] < [Index]
and x[Col4] = [Col4]
and [Col2] = null
)[Col2]
)
= 0
then null
else [Col1],
Replacer.ReplaceValue,
{"Col1"}
)
thanks
Hello Anonymous
this is what I asked you... what in this code need to be dynamic. To make your code dynamic you have to use a function instead of a field or column reference like [Col2].
For example you are using a row-reference (SelectRows) and writing x[Index] where x is representing a row. In this case you need to use the function Record.Field and writing Record.Field(x,YourTextVariableOfFunctionForIndex). When you need to reference a column like you did in Table.SelectRows(.....)[Col2] use Table.Column instead like Table.Column(Table.SelectRows(....), YourTextVariableOfFunctionForCol2)
Hope I was clear enough
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
6 Replies
Hi Anonymous
#"Previous Step" refers to the result of the previous step in the query. I'm expecting that the result is a table so what you'd need to do is pass that table into the custom function. and adjust references to #"Previous Step" as done in the query below to refer to the function parameter name instead. Here's the function:
(TheTable as table) => Table.ReplaceValue( Table.FillUp( Table.AddColumn( TheTable , "Col1", each if [Col2] <> null then [Col3] else null), {"Col1"} ), each [Col1], each if List.NonNullCount( Table.SelectRows( TheTable , (x) => x[Index] < [Index] and x[Col4] = [Col4] and [Col2] = null )[Col2] ) = 0 then null else [Col1], Replacer.ReplaceValue, {"Col1"} )Can you please share the table that resulted from the #"Previous Step" that you took the code from. I'd like to see what that table looked liek so I can understand what columns there are. You can refer to columns by position rather than name so that is probably the way to go.
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.Hello Anonymous
this depends on what you would like to make dynamic or not. I see that you are referencing a lot of columns in your query like [Index], [Col2] etc. I your future tables are looking all like this (that they have the same structure) then you can simple use this function
(tbl as table) => Table.ReplaceValue( Table.FillUp( Table.AddColumn( tbl , "Col1", each if [Col2] <> null then [Col3] else null ), {"Col1"} ), each [Col1], each if List.NonNullCount( Table.SelectRows( tbl , (x) => x[Index] < [Index] and x[Col4] = [Col4] and [Col2] = null )[Col2] ) = 0 then null else [Col1], Replacer.ReplaceValue, {"Col1"} )If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi Jimmy801 PhilipTreacy ,
Thanks for your efforts about this, the following are the table that I am using. The Col1 should be the result of the custom function. The table without Col1 will be the "previous step". The Col2 will be switched for future use (e.g. to be replaced by Col5), therefore Col2 will be considered as one of variables.
I can create a function as you mentioned. However, when I try to use this function in next step in the same table (where previous step generated), from the invoke custom fuction, what I can select is just tables or columns. How can I select "previous step" as the table I want to call?
Also, as "Col1" will be the new column, how to define it as a column and how to give the name to it? I may need create several columns using the same function but the name of "Col1" and "Col2" will be different.
Another question is the performance of using such formula is quite bad even in Power BI not in Excel, I tried buffer function, while the diagnse tool in Power Query of PBI resulted the same duration time. I also tried to do the similar thing in Dax and the PBI just said no enough memory to conduct such operation, therefore I try M instead. Any idea how to optimize the formual to a better one so that the refreshing time can be reduced. I have 40000 row for such data.
Col4 Col3 Col2 Index Col1 ZSLDLM ZSLDLM-102 CW101 26 ZSLDLM ZSLDLM-103 25 ZSLDLM-105 ZSLDLM ZSLDLM-104 24 ZSLDLM-105 ZSLDLM ZSLDLM-105 SH101,CH101 23 ZSLDLM ZSLDLM-201 22 ZSLDLM-202 ZSLDLM ZSLDLM-202 CH101 21 ZSLDLM ZSLDLM-203 20 ZSLDLM-204 ZSLDLM ZSLDLM-204 CH101 19 ZSLDLM ZSLDLM-205 18 ZSLDLM ZSLDLM-206 17 ZSKAIN ZSKAIN-102 16 ZSKAIN-205 ZSKAIN ZSKAIN-101 15 ZSKAIN-205 ZSKAIN ZSKAIN-103 14 ZSKAIN-205 ZSKAIN ZSKAIN-104 13 ZSKAIN-205 ZSKAIN ZSKAIN-105 12 ZSKAIN-205 ZSKAIN ZSKAIN-109 11 ZSKAIN-205 ZSKAIN ZSKAIN-201 10 ZSKAIN-205 ZSKAIN ZSKAIN-202 9 ZSKAIN-205 ZSKAIN ZSKAIN-204 8 ZSKAIN-205 ZSKAIN ZSKAIN-205 CH101 7 ZSJINS ZSJINS-101 6 ZSJINS-204 ZSJINS ZSJINS-201 5 ZSJINS-204 ZSJINS ZSJINS-202 4 ZSJINS-204 ZSJINS ZSJINS-203 3 ZSJINS-204 ZSJINS ZSJINS-204 SH102 2 ZSJINS ZSJINS-205 1 Hello Anonymous
copy paste the function into a new blank query and call in YourFunction
Then go to your original query, right click on your last step and select "Insert step after"
Go to the formula bar and apply your function like this.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
