Create a static date when value appears

[Anonymous]

Hi All,

I have been putting off posting (out of nervousness really) for weeks and trawling the forums for a solution but I can't seem to find one.

I have a data set of Id's and status's and periodically these status's change but my original data source does not give me a date when this changes. I could update this every day by hand and load into BI but that would be very time consuming.

I have been trying through Power Query to figure a way to add a static date of when the value appears but not overwrite on every refresh until the value changes. 

The end game is to have how long anyone stays at a particular status until they move on which I can do in DAX  if i can get the date field before hand but its tripping me up.

Does anyone have any ideas please?

Below an example file.

ID	Name	Status
A0223207	Chris Ham	Researched
A043882	Richards Potts	Development
A09907	Dan Mitchell	Researched
A001359	Colin Cooper	Researched
A00101910	Steve Downie	Close

Thank you

Chris

[v-danhe-msft]

Hi @Anonymous ,

What is your desired result when a value changes? Could you mean you want to keep the previous data regardless of a value changes? Where shoud the previous data to be and where should the new data show?

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft ,

Thanks for coming back to me.

In an ideal world I would like the data to look something like this.

ID

Name

Researched

Research Start Date

Researched End Date

Developed

Developed Start Date

Developed End Date

Close

Close Start Date

Close End Date

Support

Start Date

End date

A186127

Chris Ham

Researched

01/04/2018

20/04/2018

Developed

20/04/2018

15/05/2018

Close

15/05/2018

But unfortunately the source is data will only give me current status, but a new file will be overwrite the existing file every day, so I was hoping some form of append might be able to do it.

The file at the moment looks like this and there are 11 possible statuses.

ID	Name	Researched
A186127	Chris Ham	Researched

Thanks

Chris