Inserting Time Stamp beside a keyword.

[ceclay]

I have this dynamic list of data in Power Query, I would like to ask if it is possible in power query to add a time stamp if the product become "Out of Stock". Below is a sample data. Can't do it on excel formula as the position of Product are moving to different rows

Product	Availability	Time Stamp
Barbeque	Available
Salad	Out of Stock
Pasta	Available
Chocolate	Available
Fruit Salad	Out of Stock

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddDateTime = Table.AddColumn(Source, "Time Stamp", each if[Availability]="Out of Stock" then DateTime.LocalNow() else null)
in
    AddDateTime

 

[ceclay]

Hi @alansidman ,
The problem with the above is the time will change every time I refresh the query. What I wanted if possible is the time stamp when a product changes from "Available" to "Out of Stock". Is that even possible?

 

[alansidman]

Instead of doing it in power query, do it in the source document with a VBA Change Event. That will only work if the out of stock is manually generated in an excel file. If the source file is somewhere else, not sure how that can be accomplished. Good Luck, unfortunately, I do not have a better solution.
EDIT:
I have googled this and found something that may be of interest, however

How to track changes in the table in Power Query - Exceed

In this article, we will show you the way to track changes you make in your Excel table by using Power Query.

exceed.hr