Forum Discussion
autofill the date column with the latest date
Hi I am in new in Power Bi but is there any way to format the table that every time i "insert" a new row in the table it automates to fill thwe date column wiht the current date.Thanks
5 Replies
- collinq
Super User
HI ShakirHossain ,
That really depends on what you are trying to do - you want every row to have today's date? OR, do you want every row to have the date that it was added? And, why isn't that in your original data source (which would be potentially the easiest depending on what you are trying to do)? Where is that row of data comgin from?
Sorry for a verty late reply, All the rows contain the date/time that they were created. I am using POwer ON write back web services to insert new data to my database. Instead me clicking the date and time to make the new entry I want the column to auto fill with current date/time, as the data is being created right now.
- collinq
Hi ShakirHossain ,
The command to insert current date you can use DateTime.LocalNow() in M code. If you use that on an "add column , custom column" then you have the information you need. If you are using DAX, the comman is Now()
HI collinq,
Thank you for replying, i am writing it back to my database from power Bi. The existing rows of the table already have correct dates but i wanted to know if i can insert a new row in power Bi that can auto fill the date to current date-time as that is when it is being created instead of me manually selecting the current date-time.
- collinq
Hi ShakirHossain ,
Based on your first sentence about writing back to a database from Power BI .... Power Bi does not natively write back to a database. There are third party tools that you can purchase that will allow you to write back.
As for putting into your dataset the date and time the current date and time you can create a new field, you can write something into M that looks to see if the field is null or has a value and if it doesn't have a value then you can put in the current date and time. The key is to make sure you are not overwriting the date and time from the last time that you brought in a different row. BUT, depending on how you are bringing this in, if the original data source does not have the data you will have to maybe create a separate and unique query that has every row's id and the date you brought it in because you are going to be overwriting it each and every time (meaning: if you are using a "field has a value" idea like I am suggesting, but, if you never have that in your data source you will have to have a separate query in your dataset that shows that you have already created that field and then you will have to merge it back for that row).
