Data Transfer - A date column added automatically each day

[YavuzDuran]

Hi All,

I have a daily run file. and I want to put an extra column of date (of run) to each file (put by query editor or as a calculated column in Power BI Desktop) and want to append the next day's file to the previous one. 

Seems possible?

Thank you

[v-jingzhang · Accepted solution]

Hi @YavuzDuran 

If you want this new date column to be populated by only the newest date, you could use DateTime.LocalNow() as @mattww  has suggested. 

However, if you want to add a date column that records every date when a daily file is appended, this is not possible with Power Query or DAX. Every time when you refresh a query, it will evaluate the M codes and DAX codes again and populate columns with the latest calculation results. It will not record older values. So if you want to, for example, add today's date to today's data, and tomorrow's date to tomorrow's data, you need to add these dates to your data source files in advance. 

If your file is covered every day with only current day's data, you need to back up old files daily to avoid losing any historical data. Power BI will not keep historical data for you. It only queries your data source to import existing data from there. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[YavuzDuran]

Thank you @v-jingzhang 

[mattww]

Hi @YavuzDuran 

So to add a column which will record the refresh date and time, in Power Query add a Custom Column with the following function

DateTime.LocalNow()

For Incremental Load, it's probably a bit too involved to explain on the thread, so I'd suggest checking out this documentation. But basically you choose a date field from your data (e.g. a Date Created or Date Modified field), and a period of time to refresh (e.g. 1 week), Power BI will then only delete and refresh records where the selected date is in the last 1 week, anything older than 1 week will be left alone when the refresh happens.

Incremental refresh for datasets in Power BI - Power BI | Microsoft Docs

It does depend on your data source though, I haven't been able to get it to work with things like APIs, but for something like a database you should be fine

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

[mattww]

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

[mattww]

Hi @YavuzDuran ,

It would be possible to populate a column with the current date/time, but are you talking about an incremental load as well? Generally when you refresh a dataset it will delete everything and re-import everything, in some circumstances you can set up an incremental load

[YavuzDuran]

Yes, incremental load as well, I need