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

     

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