cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hello @Fei-Lau 

 

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

View solution in original post

6 REPLIES 6
Highlighted
Resident Rockstar
Resident Rockstar

Hi @Fei-Lau 

#"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.

Highlighted
Super User II
Super User II

Hello @Fei-Lau 

 

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

Highlighted

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. 

 

Col4Col3Col2IndexCol1
ZSLDLMZSLDLM-102CW10126 
ZSLDLMZSLDLM-103 25ZSLDLM-105
ZSLDLMZSLDLM-104 24ZSLDLM-105
ZSLDLMZSLDLM-105SH101,CH10123 
ZSLDLMZSLDLM-201 22ZSLDLM-202
ZSLDLMZSLDLM-202CH10121 
ZSLDLMZSLDLM-203 20ZSLDLM-204
ZSLDLMZSLDLM-204CH10119 
ZSLDLMZSLDLM-205 18 
ZSLDLMZSLDLM-206 17 
ZSKAINZSKAIN-102 16ZSKAIN-205
ZSKAINZSKAIN-101 15ZSKAIN-205
ZSKAINZSKAIN-103 14ZSKAIN-205
ZSKAINZSKAIN-104 13ZSKAIN-205
ZSKAINZSKAIN-105 12ZSKAIN-205
ZSKAINZSKAIN-109 11ZSKAIN-205
ZSKAINZSKAIN-201 10ZSKAIN-205
ZSKAINZSKAIN-202 9ZSKAIN-205
ZSKAINZSKAIN-204 8ZSKAIN-205
ZSKAINZSKAIN-205CH1017 
ZSJINSZSJINS-101 6ZSJINS-204
ZSJINSZSJINS-201 5ZSJINS-204
ZSJINSZSJINS-202 4ZSJINS-204
ZSJINSZSJINS-203 3ZSJINS-204
ZSJINSZSJINS-204SH1022 
ZSJINSZSJINS-205 1 
Highlighted

Hello @Fei-Lau 

 

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"

Jimmy801_0-1603952474632.png

 

Go to the formula bar and apply your function like this. 

Jimmy801_1-1603952523753.png


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

Highlighted

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 

 

 

Highlighted

Hello @Fei-Lau 

 

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors