Power Query date to text

[cmcreynolds]

Hello again forum folks -

To obtain a unique identifier, I change a date to text and then concatenate with a name field - all of this within Power Query. I am grabbing the data from about 16 CSV files all in the same structure and no formatting (that I can see?).

For the first (alphabetically) file, PQ keeps the formatting of the date the same (7/7/2015) but for all the other files, it adds leading zeroes (07/07/2015) - even though the date field is without zeroes (7/7/2015)!!

Does anyone know why PQ is doing this?

Ultimately I want the text file without the zeroes.


Within the same PQ Query editor window (the date field is in green, the text field is the far right column):

With zeroes:

Imgur

Without zeroes:

Imgur

 

[ImkeF]

I've experienced that PQ isn't too good with "no formatting". It will then apply or assume it's own formats - depending on very small details
So it's best to specifically apply formats before you start querying further.

Re your example - try Date.ToText: There you can specify the desired Output-Format.

 

[cmcreynolds]

I just replaced all the single digits within the column (i.e. "08/" with "8/"). I am on a timeline right now, so it was the only way I could ensure single digits for months. Would mm/dd/yyyy not produce 08/03/2015, too? Anyway, I'll investigate further.

Thanks.

 

[ImkeF]

yes, thats the example in the Manual. You could use m/d/yyyy instead.

But if it's "just" about a technical field - why bother with formatting - convert your Dates to numbers instead: Number.From

Imke