Forum Discussion

Anton999's avatar
Anton999
New Member
3 years ago

Changes to historical data

Hello, 

 

I want to create a table with changes to historical data without overwriting of existing data. 

 

Example:

 

Data is loaded to PowerBi from Excel file: 

nmmn

DateDate of changeProductOrder

Order Line

Price
01.01.2023  01.01.2023  Product A 00101 200
01.01.2023  01.01.2023  Product B 00102 300
01.01.2023  01.01.2023  Product B 00201 300

 

Every day I load the updated data and there might be changes to the existing rows. In this case I don't want to just overwrite the existing data, but instead create a row with correction.

 

Data on the next day (new line is added, one line is removed and one line is changed): 

 

DateDate of changeProductOrderOrder LinePrice
01.01.2023 02.01.2023 Product B  001 02400
01.01.2023 01.01.2023 Product B  002 01300
02.01.2023 02.01.2023 Product A  003 01200

 

I want the final table looks like this(with initial lines, but also with the correction to balance them): 

 

DateDate of changeProductOrderOrder LinePriceType
01.01.2023 01.01.2023 Product A 001 01 200 Order
01.01.2023 01.01.2023 Product B 001 02 300 Order
01.01.2023 01.01.2023 Product B 002 01 300 Order
02.01.2023 02.01.2023 Product A 003 01 200 Order
02.01.2023 02.01.2023 Product B 001 02 100 Changed
02.01.2023 02.01.2023 Product A 001 01 -200 

Removed

 

Is it possible to create something like this in PowerBi?

 

Thank you in advance!

2 Replies