Forum Discussion

Regular Visitor

3 years ago

Solved

Work with dates

Hello, I am importing charging data from a charging station for electric cars. To be able to calculate the cost of the charged energy, the date when the charging was started is important. The cost ...

3 years ago

Hi Alexandre,

now your code work 😉

I have written a query with the code (see below). I call this query in a custom column. It always finds the correct price!

Thanks for your help!!!

(FieldInput as datetime) =>
let
    qSource = Excel.CurrentWorkbook(){[Name="tbl_CostPerTime"]}[Content],
    qRow = Table.SelectRows(qSource, each ([ValidFromDate]<=FieldInput)),
    qContent=
            Table.Sort(qRow,{{"ValidFromDate", Order.Ascending}})[Value]{Table.RowCount(Table.Sort(qRow,{{"ValidFromDate", Order.Descending}})) - 1} 
in
qContent

RalphHoffmann

Regular Visitor

3 years ago

Hi Alexandre,

thank you for your help. Your suggestion does not quite work. As you can see in the screenshots, the date from which a price is valid is entered in the stored order, but not matching the start day of the loading process.

However, the prices must be assigned as follows:

- Charge start 20.02.2022, price from 01.01.2022
- Charge start 07.03.2022, price from 01.03.2022
- Charge start 12.09.2022, price from 01.09.2022

Result:

Result