Keep Old Values

[coyote_ptm]

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?

[parry2k]

@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/38490

[coyote_ptm]

I'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

[v-rzhou-msft · Accepted solution]

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 Zhou

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

[Ashish_Mathur]

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?