Need Help Turning my Custom Column into a Custom Function

[ChromeMystic]

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? 

[AlexisOlson · Accepted solution]

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]

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!

[ChromeMystic]

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]

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]

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!

[v-yanjiang-msft]

Hi @ChromeMystic ,

Have you read @AlexisOlson 's solution, if it still not be resolved, could you please explain more or show your expected result.

Best Regards,
Community Support Team _ kalyj

[ChromeMystic]

Actually I didn't 🙂 Thanks for the headsup

[Vijay_A_Verma]

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]))