Forum Discussion

ChromeMystic's avatar
ChromeMystic
Helper I
4 years ago
Solved

Need Help Turning my Custom Column into a Custom Function

I am quite new to the might of powerquery, and I am currently trying to build a lookuptable for our KPI Values. With Help from these forums, I managed to build a working formula for my Data. See the other Topic 

 

 

Table.SelectRows(sedKPI, 
    		(sedKPI) =>(sedKPI[validFrom] <= [date]) and
       		(sedKPI[validTo]  >=  [date]) and 
       		(sedKPI[KPI] = "debugKPI")
        	)[green]{0}

 

I have no tried for about 2 hours to build this into a custom function, as I will have to lookup several values for multiple KPI. I would like to create a custom function, that gets the parameters of the KPI and the value it should look up ("debugKPI" and "green" in this case.

I researched several guides and they all basically copy the advanced editor code and cut out all the stuff before the calculation needed. This led me to a function that looks like this:

 

(KPI) =>
let
  #"KPIValue" = Table.AddColumn(#"Remove columns", "debugKPIG", each Table.SelectRows(sedKPI, 
    		(sedKPI) =>(sedKPI[validFrom] <= [date]) and
       		(sedKPI[validTo]  >=  [date]) and 
       		(sedKPI[KPI] = KPI)
        	)[green]{0})
in
  #"KPIValue"

 

The Reference to Remove colums is obviously throwing an error: 

Expression.Error: The name 'Remove columns' wasn't recognized. Make sure it's spelled correctly.

 

I tried a ton of variations, most of them complain that there are not enough parameters and some just hang for ages and don't really give me any meaningful feedback.

 

How should I go about turning this into a working function? 

Need Help
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I think you need more input variables for your function.

     

    Try this:

    (D as date, KPI as text, Col as text ) as number =>
let
    Lookup =
        Record.Field(
            Table.SelectRows(sedKPI, 
                (sedKPI) =>
                    (sedKPI[ValidFrom] <= D) and
                    (sedKPI[ValidTo] >= D) and 
                    (sedKPI[KPI] = KPI)
            ){0}, Col
        )
in
    Lookup

     

    You can call the function like this:

     

    I've updated and attached the pbix that v-yanjiang-msft provided in the prior thread in case you want to take a closer look.

     

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I think you need more input variables for your function.

     

    Try this:

    (D as date, KPI as text, Col as text ) as number =>
let
    Lookup =
        Record.Field(
            Table.SelectRows(sedKPI, 
                (sedKPI) =>
                    (sedKPI[ValidFrom] <= D) and
                    (sedKPI[ValidTo] >= D) and 
                    (sedKPI[KPI] = KPI)
            ){0}, Col
        )
in
    Lookup

     

    You can call the function like this:

     

    I've updated and attached the pbix that v-yanjiang-msft provided in the prior thread in case you want to take a closer look.

     

    • ChromeMystic's avatar
      ChromeMystic
      Helper I
      4 years ago

      Thanks so much - this worked. I think i got about 90% there in my tests, but the small changes you made were what i needed!

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Your function has to be following

    (sedKPI) =>
let
  #"KPIValue" = Table.SelectRows(sedKPI, 
    		(sedKPI) =>(sedKPI[validFrom] <= [date]) and
       		(sedKPI[validTo]  >=  [date]) and 
       		(sedKPI[KPI] = KPI)
        	)[green]{0}
in
  #"KPIValue"

    In Query, you are calling this function from - let's say this function's name is fxABC. Let's say you are calling this fxABC for a column named Temp.

    #"Added Custom" = Table.AddColumn(#"Remove columns", "debugKPIG", each fxABC([Temp]))

     

  • ChromeMystic's avatar
    ChromeMystic
    Helper I
    4 years ago

    Unfortunately your solution doesn't work for me.

    [date] references to the date field in the table I am trying to run the function in.

     

    the Values I wan't to add dynamicaly are KPI for the name of the KPI the function has to search for, and [green]  to select the column where the value is stored (green, yellow,....)


    Also shouldn't the start of the function be (KPI)=> as that is the Variable I try to call later in the function?

     

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      I need to see your pbix file to see the solution you are working on to give right advice. Ideal will be to upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.

      • ChromeMystic's avatar
        ChromeMystic
        Helper I
        4 years ago

        I wasn't in the office since you last posted, so I only got around to create the mockup today.

         

        https://1drv.ms/u/s!AqDYC-h9nk9olv9B3Fd1VhSDcAso6Q?e=T2HmtY

         

        As you see I am creating a lookup table. And the two Variables I would like to use in a function are the name of the KPI and the Column it has to look for (if thats no possible, i can write different functions for the different columns).

        The date value is taken from the date column in sedKPIDate and conpared to the validFrom and validTo values in sedKPI

         

        Thanks a ton for your help!