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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors