Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ChromeMystic
Helper I
Helper I

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? 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

AlexisOlson_0-1651246372587.png

 

You can call the function like this:

AlexisOlson_1-1651246433226.png

 

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.

 

View solution in original post

8 REPLIES 8
AlexisOlson
Super User
Super User

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

AlexisOlson_0-1651246372587.png

 

You can call the function like this:

AlexisOlson_1-1651246433226.png

 

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.

 

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
Helper I
Helper I

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?

 

 

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.

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!

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

Actually I didn't 🙂 Thanks for the headsup

Vijay_A_Verma
Super User
Super User

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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors