Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
I must now include in the calculation of parking fees
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
Solved! Go to 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
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
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imke,
that's does't work. I get the error, that this cant't convert to a list?🤔