Power Query Date Format

[cmcreynolds]

My source table (in an Excel worksheet) has a column that I use just to summarize by year and month (e.g. 2014-March). I know it's not pretty, but I find it useful in some of my work. HOWEVER, I'm finding that Power Query automatically translates it to 3/1/2014, even though in the source table it's formatted as text.

How can I preserve my format through the PQ-PP funnel?

 

[Gordonik]

In PQ editor:

-> select the column with dates
-> select the data type to "text"

 

[cmcreynolds]

@Gordonik - I tried that and it would just change 3/1/2014 from date to text, not back to 2014-March.

The issue was that PQ is automatically assigning formats (in the Advanced editor) as soon as the table was pulled in - I didn't assign any formats. So, to solve it, I went into the Advanced Editor and changed it THERE, instead of just selecting the column.

 

[olivierhbh]

Hello,

When importing data, PQ indeed assigns formats to columns. To cancel that just delete the "Changed Type" instruction either in the advanced editor or in the Query Settings in "Applied steps".

 

[cmcreynolds]

Has it always done this, though? I've never seen it automatically assign formats before....at least, I don't remember seeing it. Is there a way to turn off this feature?

 

[olivierhbh]

Well so far as I remember yes.
You can turn it off going to Options and Settings>QueryOptions>Data Load and untick Automatically detect column types and headers for unstructured sources
Olivier.

 

[cmcreynolds]

Oh, you know what - I think I did this about 6 months ago. Maybe with the last update it "re-checked" it. Thanks, though!