Date Format

[Chippy]

How can I make this text format in a dd mm yy format? Using the normal DateValue it seems to give me dates in future eg 7 Dec instead of 11 July?

[PhilipTreacy]

@Chippy 

Have you looked at the file I emailed you?

Regards

Phil

[Chippy]

Thanks Phil, Yes, I took your approach and now using Power Query instead of the DAX and I seem to have more success.

Another query has come up, that is: my data is sourced from another 3rd party application, so I need to download the data in Excel. I tried downloading the data and saved over the old data, keeping the same file name but the BI model says, "The key didn't match any rows in the table". What would be the best practise for refreshing the excel data, to ensure all the added custom queries I have added in Query are preserved?

thanks

Chris

[PhilipTreacy]

Hi @Chippy 

Best to start a new post for a different problem and accept a solution for this one.

That error can indicate that names of sheets or columns are different in the file(s) you are trying to import compared to the initial file you used to set up the query.

Best practice is to keep all files you import with the same query to have exactly the same structure (e.g. sheet names, column names) and data types in the columns.

Regards

Phil

[Chippy]

Thanks, This did not seem to work..I am wondering if it is due to the date formats not being consistent eg days drop the leading zero but months have a leading zero.  

[PhilipTreacy]

Hi @Chippy 

No that shouldn't matter - download this file for an example.

When you say it didn't seem to work - why do you say this?  What result did you get when you tried what I suggested?

Please provide the actual data you are using - even better would be the file with the data in it. 

Regards

Phil

[Chippy]

https://froneri365.sharepoint.com/:u:/r/teams/AU_Projects/Dashboards/Controlled%20Docs/Date%20Format...

I am not sure if this is the best way to sahre as I can not attach a file..bit in this sample you can see the original dates when converted to datevalue reverses some dates from dd mm to mm dd 

[PhilipTreacy]

Hi @Chippy 

I can't access that, it requires a login to your system.

You can put the file on OneDrive, Google Drive, Dropbox etc and then share the link to it.

Regards

Phil

[Chippy]

ok, 

[Chippy]

Which email do I share the onedrive link with?

[PhilipTreacy]

@Chippy 

I get this when I try to login and access that file

I am the Admin for my company.  No idea what it is going on about but it seems your org blocks 'at risk' users whatever that means.

[Chippy]

Can you try again as I changed your access to Edit

[PhilipTreacy]

@Chippy 

As we are both online now, can you put the file somewhere public, send me the link, I'll download it, then you can delete the file?

[PhilipTreacy]

@Chippy 

Same, account is blocked

[PhilipTreacy]

@Chippy 

You can just share the link here?

I'll PM you my email, check your messages on this forum.

[PhilipTreacy]

Hi @Chippy 

Use Change Type -> Using Locale then choose a locale that uses the d/m/y format e.g. Australia or the UK

NOTE: In this image I've chosen the Data Type : Date, if you want to preserve the time part of your data, choose Date/Time instead

Regards

Phil