Forum Discussion

RalphHoffmann's avatar
RalphHoffmann
Regular Visitor
4 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 ...
Interesting Links
Need Help
Tutorial Requests
  • RalphHoffmann's avatar
    RalphHoffmann
    4 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

     

_AlexandreRM_'s avatar
_AlexandreRM_
Icon for Helper II rankHelper II
4 years ago

Hello RalphHoffmann , here is what I suggest you to do to achieve your goal:

 

I have 2 tables in my Excel sheet, both imported and typed in PowerQuery.

 

In the 'Orders' table I aded a custom column to calculate the correct price to use. Here is the formula explained:

let
t = Prices, //we store the 'Prices' table in a variable
orderDate = [Order date], //we store the row order date in a variable
filtered = Table.SelectRows(t, each [Date] <= orderDate), //we filter the 't' table to remove all prices after the order date
sorted = Table.Sort(filtered,{{"Date", Order.Ascending}}), //we sort the 'filtered' table from the oldest date to the newest
price = sorted[Price]{Table.RowCount(sorted) - 1} //the price we want is on the last row of the 'sorted' table: it's the latest price date before the order date
in
price

 

This could be simplified (and optimized) in these 2 ways:

remove the t = Prices step (I did this because my screen isn't very large, and I didn't wanted to have a single instruction on 2 rows),

set the sorting step directly in the Prices table.

 

Hope this helps.

  • RalphHoffmann's avatar
    RalphHoffmann
    Regular Visitor
    4 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

     

     

     

     

    Table with Prices:

     

    User defined Column

    Userdefined column

    Is it possible that the runtime must be informed in line 4 from which table the column [ValidFromDate] comes?

    • _AlexandreRM_'s avatar
      _AlexandreRM_
      Icon for Helper II rankHelper II
      4 years ago

      Hello RalphHoffmann , you miss some adaptations to the function code:

      Try to replace (row 6)

      sorted = Table.Sort(filtered,{{"Date", Order.Ascending}})

      by

      sorted = Table.Sort(filtered,{{"ValidFromDate", Order.Ascending}})

       

      And to replace (rows 8 & 9)

      in tbl_CostPerTime

      by

      in price

    • RalphHoffmann's avatar
      RalphHoffmann
      Regular Visitor
      4 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