Detecting the previous row in Power Query - handling a text file

[Anonymous]

Hi,

I need to import more rows from several stock info text files, treating them inside power query and then producing some KPIs.

I need to manage a granularity at day and hour level.

The record layout for a text file is:

WH_SITE YYYYMM B_DDhh-E_DDhh MAIN_VALUE OVR OB_DDhh-OE_DDhh VALUE_TO_OVERRIDE OVR OB_DDhh-OE_DDhh VALUE_TO_OVERRIDE,

where

WH_SITE = Warehouse location code

YYYYMM = validity forecast year and month

B_DDhh = validity begin forecast day and hour

E_DDhh = validity end forecast day and hour

MAIN_VALUE = initial stock value, valid between B_DDhh and E_DDhh

OVR = optional section to override the initial value for a specific validity period (I could have 0 or 1 or max 5 OVR sections)

OB_DDhh-OE_DDhh = begin and end of a validity period for a stock value to override

VALUE_TO_OVERRIDE = stock value that overrides the initial stock value for a specific validity period

Here a record sample:

SITE01 202104 0709-0718 5000 OVR 0712-0713 4500 OVR 0715-0716 4200

I've applied several transformation steps inside Power Query, obtaining a such result:

but I need to obtain a such result, before using some DAX logics to obtain the final KPIs:

The override operation occurs after the related end of the validity period.

The override sections are optional, up to five ones.

I thought to detect the previous row to understand the passage from the old stock value to the new stock value to override. I cannot adding an index, I need to handle some millions of rows (I've expanded the validity period to split in date + time, hour for hour), perhaps I need to search the previous row respect to the WS_SITE and the VALIDITY_DATETIME less one hour.

Any suggests to me, please? Thanks

[ERD · Accepted solution]

@Anonymous ,

You can create custom columns with if/else conditions and then delete old column. Something like this:

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Anonymous]

Sorry, I've tried more time to pasted the worked rows, but unsuccessfully!

Here, the last attempt accomplished:

WS_SITE VALIDITY_DATETIME INITIAL_STOCK_VALUE OVR_BEGIN_DATETIME_1 OVR_END_DATETIME_1 OVR_STOCK_VALUE_1 OVR_BEGIN_DATETIME_2 OVR_END_DATETIME_2 OVR_STOCK_VALUE_2
SITE_01 07/04/2021 09:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 10:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 11:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 12:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 13:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 14:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 15:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 16:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 17:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200
SITE_01 07/04/2021 18:00 5000 07/04/2021 12:00 07/04/2021 13:00 4500 07/04/2021 15:00 07/04/2021 16:00 4200

[AlB]

Hi @Anonymous 

Suggestion 1: Share your data in text-tabular format so that people trying to help can copy the table contents easily and run some tests. Just paste the table contents here, or share an excel file or a pbix with the data

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.