Forum Discussion
Keep Old Values
I have a requirement that I don't seem to understand how to do.
I have a table exported from an external software package ( it's exported to csv and picked up for refresh ) that contains:
employee_id INT, -- unique employee ID
chg_pct INT -- number between 0 and 100
The current system does not track changes when the ratio changes. To retain history, I'd like to save this data in PBI as:
employee_id,
chg_pct ,
changed_date
Look at exported employee_id, chg_pct, check to see if the chg_pct is different than the latest row and insert a new one if it is.
Is this possible?
Hi coyote_ptm ,
Firstly, the same question as Ashish_Mathur mentioned, what does your table looks like?
Empl PCT 1 50 2 55 3 60 Or
Empl PCT Last Changed 1 50 2/1/2021 2 55 3/1/2021 3 60 4/1/2021 I think you need [Last Changed] column to determind the last changed date of data for each employee.
You want to add a new row if some employee's PCT is changed instead of replacing. However refresh will replace the old value by new value. So I think refresh is not suitable. Here I suggest you to load the new data , use Append function and remove the duplicates.
For reference: Append queries
Select columns, right click and remove duplicates. Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
coyote_ptm not very clear what you are looking for. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490I'd like to retain old values for a key value.
The original dataset example is a CSV that contains employee ID and percentage. The data is exported to the CSV nightly.
Empl PCT 1 50 2 55 3 60 The table I'd like in PBI is Employee ID, percentage, Last changed date. Example
Empl PCT Last Changed 1 50 2/1/2021 2 55 3/1/2021 3 60 4/1/2021 I would like PowerBI to import changed values as new rows. For example, employee 1 percentage was changed to 75 on 01/10/2022. The refreshed data would still have the original data, but a new row for when the row was changed. If employee 1 percentage changed to 75 on 01/10/2022. The CSV that is export would only have 1 row still for empl with a new PCT:
Empl PCT 1 75 2 55 3 60 But the new table should be:
Empl PCT Last Changed 1 75 2/1/2021 2 55 3/1/2021 3 60 4/1/2021 1 75 1/10/2022 Hi,
From where will the dates appearing under the Last changed column appear. In the first and the third table, there is no Date field at all?
