cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmscorca
Helper IV
Helper IV

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

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:

 

pmscorca_0-1621708044294.png

 

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

 

pmscorca_1-1621708157176.png

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

1 ACCEPTED SOLUTION
ERD
Super User
Super User

@pmscorca ,

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

ERD_0-1621842205965.pngERD_1-1621842224110.pngERD_2-1621842284745.png

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

View solution in original post

3 REPLIES 3
ERD
Super User
Super User

@pmscorca ,

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

ERD_0-1621842205965.pngERD_1-1621842224110.pngERD_2-1621842284745.png

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

View solution in original post

pmscorca
Helper IV
Helper IV

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
Super User
Super User

Hi @pmscorca 

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

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors