Power Query Distorting Columns Formatted as 'Date'

Question

Howdy, 

I am using Power Query on Office 13 to merge tables from several excel files into a master table. In these source tables there are two date columns A and B. Date column A contains only dates. Column B contains blanks, dates and sometimes text - always the text "Enter Date"

When I import the data into Power Query I change all the blanks and "Enter Date" values to null and then format the column to date. Then, in the merged table I have a step that also formats this column to a date. 

From the master table I have created many pivot tables and charts, all of which are dependent on these date columns. With these pivot tables I have some timeline slicers set up as well. 

For some reason, sometimes when the data is updated my slicers are deleted from my master workbook! Sometimes its dates from column A, sometimes B. When I check the master table (both in excel and power query) columns A and B are recognized as date columns. 

The problem is, I can't find any consistency. I have 7 of these master table excel files (1 for each department) and some departments are always affected after an update, some are never affected some are sometimes affected. We update files once a week and I'm getting tired of reapplying all these slicers that go missing. We all run Office 13 with the latest version of Excel and Power Query, although some of us run windows 7 and 10 - but there has been no consistency between failures and versions of Windows either.  

Can anyone help? Thanks so much in advance.

Answer 1

Hi,

In the Power Query window, don't just change the format to Date.  Try this

1. Right click on a date column
2. Go to Change Type > Using Locale > Data Type > Date
3. Under Using Locale, select the correct option
4. Click on OK

Do this for the other Date columns as well.  Let me know if this works.

Answer 2

Hi R.o.b.H,

We haven’t heard from you for a while. Could you let me know any updates at your convenience?

Regards,

Shirley

Answer 3

Thanks for reaching out. I'm still updating the workbooks. Maybe today I'll finish them up.

Answer 4

R.o.b-H

We haven’t heard from you for a while. Do you need further assistance? 

Regards，

Shirley

Answer 5

That sounds like an excellent idea. I was unaware of the locale feature. Let me update the workbooks and I'll keep you posted. Thanks.