Date created in Power query

[MarcUrdang]

When bringing in data via Power Query I see that the DATE CREATED is showing yesterday's date? I need to be able to filter on only bringing in new data so I would need to filter on TODAY's data only .. .but if the date is incorrect then how would one do this?

thanks

Marc

[v-easonf-msft · Accepted solution]

Hi , @MarcUrdang 

Not clear what you want.

If what you want is just to filter  data equal today  in powerquery ,you can  try the following m query formula :

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] =  DateTime.Date(DateTime.LocalNow()))

If not ,please explain it more and kindly share your screenshoot .

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

[v-easonf-msft · Accepted solution]

Hi , @MarcUrdang 

Not clear what you want.

If what you want is just to filter  data equal today  in powerquery ,you can  try the following m query formula :

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] =  DateTime.Date(DateTime.LocalNow()))

If not ,please explain it more and kindly share your screenshoot .

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

[MarcUrdang]

Hi ... I am not sure I was being clear enough so apologies for that .. what I mean is that whene you 'get data' and you point to a folder in OneDrive or SharePoint you have the option to filter in only those file you want to combine. One of those filters is based on 'time created'. However it seems that PBI is set to another time zone. I am in South Africa which is GMT+2 .... so if I create an Excel file at 08h00 the time in the 'create date' field shows perhaps 23h59.

So if I then filter on TODAY then I get a different number of files.

Any thoughts?

[v-easonf-msft · Accepted solution]

Hi ,  @MarcUrdang 

Unless you are in the  UTC time zone, you and your colleague must account for the time offset you experience. 

You can use DateTime.AddZone function to achieve your requirement. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.

DateTime.AddZone([datetime], an offset )

You may  refer to these related post.

https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/m-p/56337

https://community.powerbi.com/t5/Desktop/Convert-utc-to-local-time-zone-using-Power-Query/m-p/45533

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

[AllisonKennedy]

From what you're describing it might actually be your SharePoint timezone that needs set, not the Power BI one if the date created date is showing as incorrect. Does the date created show properly in OneDrive/SharePoint? You can set regional settings for Power BI data load in the File > Options and there are a few different time zone options for working with dates in Power BI. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.
Refer if this can help :https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/
Appreciate your Kudos.

[AllisonKennedy]

@MarcUrdang  Where are you pulling the data from? What DATE CREATED info are you referring to? Is this a column in the data itself? Could it be a timezone issue?