Function with two Parameter

[RalphHoffmann]

Hello Folks,

today I have another question.

I have a Table like this:

Key	Value	ValidFromDate
Company1	False	01.01.2022
Company2	False	01.01.2022
Company1	True	01.03.2022
Company3	False	01.03.2022
Company1	False	01.10.2022

In this table (tbl_HasToPayParking) it is recorded whether a company has to pay parking fees after the end of a charging process of an electric car or not. Whether perk fees have to be paid or not can change at any time.

Task:
In an export (tbl_ChargingData) of the charging station is specified for all charging processes:

1. Start of the charging process (date and time)
2. End of the charging process (date and time)
3. Time during which the electric car was connected to the charging station (in minutes).

I must now include in the calculation of parking fees

1. filter the table tbl_HasToPayParking according to the companies, then
2. search in it for the next most recent date compared to the charging time from tbl_ChargingData ([ValidFromDate] <= [Charging_StartDay])
3. read the result (True/False) from the [Value] column of the tbl_HasToPayParking table.

My code is this one:

(pCompany as text, pDate as datetime) =>
let
    qSource = Excel.CurrentWorkbook(){[Name="tbl_HasToPayParking"]}[Content],
    qRow = JoinKind.Inner(qSource, Table.SelectRows(qSource, each ([Key]=pCompany)), qSource, Table.SelectRows(qSource, each ([ValidFromDate]<=pDate)))
    //qContent=  
    //        if Table.IsEmpty(qRow)=null
    //            then  false 
    //            else [Value]{Table.RowCount(Table.Sort(qRow,{{"ValidFromDate", Order.Descending}})) - 1} 
in
qRow //qContent

Do I start the function with

= Test_GetCompanies("Company1", #datetime(2022, 09, 30, 0, 0, 0))

pCompany = "Company1
pDate = 30.09.2022

I get this error message

Fehler in der Abfrage ''. Expression.Error: Der Wert "0" kann nicht in den Typ "Function" konvertiert werden.

Error in the query ''. Expression.Error: The value "0" cannot be converted to the type "Function".
Details:
Value=0
Type=[Type]

Can anyone help me?

Ralph

[RalphHoffmann · Accepted solution]

I think I have the solution with this Code:

(pCompany as text, pDate as datetime) =>
let
    qSource = Excel.CurrentWorkbook(){[Name="tbl_HasToPayParking"]}[Content],
    qDate= Table.SelectRows(qSource, each ([ValidFromDate]<=pDate)),
    qRow = Table.SelectRows(qDate, each ([Key]=pCompany)),
    qContent=  
            if Table.IsEmpty(qRow)=true
                then  false 
                else Record.Field(qRow{Table.RowCount(Table.Sort(qRow,{{"ValidFromDate", Order.Descending}})) - 1},"Value")
in
qContent

#############

UPDATE

#############

Changed if Table.IsEmpty(qRow)=null

to if Table.IsEmpty(qRow)=true

[ImkeF]

Hello @RalphHoffmann ,
that's because you are using the function "JoinKind.Inner" in a wrong way.
Please try this instead:

(pCompany as text, pDate as datetime) =>
let
    qSource = Excel.CurrentWorkbook(){[Name="tbl_HasToPayParking"]}[Content],
    qRow = Table.Last(Table.SelectRows(qSource, each ([Key]=pCompany) and [ValidFromDate]<=pDate)){0}[ValidFromDate]
    
in
qRow

[RalphHoffmann]

Hi Imke,

that's does't work. I get the error, that this cant't convert to a list?🤔