Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
Jimmy
Hi @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
Guess I did not describe my questions clearly. I know how to make a custom function, and making entire function as a table will not solve my issue as I need several parameters to be dynamic. E.g. Col1, Col2, and Previous Step.
Part of the function is to create a new column, however, once I input the new column name (which is to replace the Col1 position in the function), the error returned saying that the column could not be found.
Also, the column name is used in some other places such as "Replacer.ReplaceValue, {"Col1"}", when creating the function, shall I separate this parameter as a different one or same one as Col1? If it is a different one, what parameter should I input, the whole {"Col1"} or just Col1 (without curley bracket and double quote)?
I tried several combination and the result is always error. I am wondering whether the it is possible to make the parameter in {"Col1"} dynamic.
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
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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.