Asking About Date Function

[Islam]

Hey Experts

i have added a column to my query and add this step targeting type the date and time once a value is entered in items column, here is the formula:

#"Added Custom" = Table.AddColumn(#"Changed Type1", "تاريخ الادخال", each if [اسم الصنف] = ""
then ""
else if [اسم الصنف]<> "" then DateTime.LocalNow()
else null),

Item Name = "اسم الصنف"

each time i refresh the query date changes but i want it to be entered just one time when i enter an item name to the query and when i delete that item name date automatically delete, Any help:)

[v-juanli-msft]

Hi @Islam 

each time i refresh the query date changes but i want it to be entered just one time when i enter an item name to the query and when i delete that item name date automatically delete, Any help:)

1. Do you want to show date instead of datetime after refreshing each time?

If you want date, please try Date.From(DateTime.LocalNow())

2.if you want to delete a column automatically, please refer to:

https://datachant.com/2016/12/09/remove-empty-columns-rows-from-table-in-excel-power-query/

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

csadfasdfsda

[Islam · Accepted solution]

hi @v-juanli-msft  

thx man for help i just figured out that i can't enter the date only once when i enter data in a specified column like in excel built in function on VBA

[Anonymous]

Hi @Islam ,

I have marked your reply as a solution, however, this is not precisely correct.

There is a workaround to achieve what you want using "incremental refreshes" - this should also work in Excel.

If you use this technique to preserve the existing data and only overlay changes the original date-time stamp will withstand consequent refreshes. 

Kind regards,

JB

[Mariusz]

Hi @Islam 

Power Query is designed to extract the data from different data sources, not to record or collect data therefore your best option would be to create the datestamp at the source.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Islam]

thx @Mariusz 

thanks for that bro but i was wondering if there is any reference for that topic and how to apply it