Question Excel data type changed by itself when using Pivot?

[Akos__]

Hey!

So I have an excel datasheet which uses PowerPivot to import data from a plain txt file. One of the columns is just dates.

I have set the date format in the Pivot editor interface, but for some reason every new entry added to the txt file (same format as all the other ones), the dates keep showing up in text format. When I delete all the data from the worksheet and click refresh, all the previous dates appear as text format too. Nothing has been changed in the Pivot syntax.

Has anyone encountered this problem before? If so, what was your solution? I tried manually selecting the date column in the worksheet and change the data type to date but it's only good until a new entry is added to the txt file, or I "delete and refresh".

 

[Ralston18]

Not sure about the import process you are using and how the excel spreadsheet is designed and formatted.

Overall though you probably just need to call/apply the applicable function.

Examples (may or may not fit your situation but do represent possible solutions):

https://docs.microsoft.com/en-us/dax/datevalue-function-dax

https://community.powerbi.com/t5/Desktop/Can-t-convert-text-to-date/m-p/663700

You can easily find other similar links.

Do be sure to back up all original data before trying and testing.

Preferably set up a test environment separate from the working/production environment to determine what conversion method will work for you.

 

[Akos__]

Not sure about the import process you are using and how the excel spreadsheet is designed and formatted.

Overall though you probably just need to call/apply the applicable function.

Examples (may or may not fit your situation but do represent possible solutions):

https://docs.microsoft.com/en-us/dax/datevalue-function-dax

https://community.powerbi.com/t5/Desktop/Can-t-convert-text-to-date/m-p/663700

You can easily find other similar links.

Do be sure to back up all original data before trying and testing.

Preferably set up a test environment separate from the working/production environment to determine what conversion method will work for you.

Let me provide some context. The txt file (source file) contains the date in this form: 2022.01.01. When imported into PowerQuery, at first the columns containing these values are by default in text. When configuring the query via the editor, we set the columns' data type to "date". So obviously it worked and the final result in the worksheet was 2022.01.01. (example date). It has been working for months and for some reason, after a while (no changes have been made in either the txt file format or the query editor) the date shows up as: 44589 (which is supposed to be 2022.01.28.) Dunno why, I see no changes in any of the settings we've made.

 

[Ralston18]

Are you able to compare the last text file that worked correctly to the text file being used/processed when the date errors began?

First step is to verify that the imported data is not the source or cause of the problem.

How is the original text (source) file created?

FYI:

https://www.contextures.com/exceldatesfixformat.html

The link provides some suggestions that should help with troubleshooting.

Again: create a test environment and use copies of real data for troubleshooting and testing.

 

[Akos__]

The source txt file is exported from an SAP program and it's always the same format, basically every new version just has some extra lines at the bottom of the file. It is imported perfectly, as seen on the screenshot I provided. I always create a test environment, I always make a copy of the original files to be tested.

I tried out the method in the video, but it just made everything worse. 2022.02.18. showed up as 44610 and it converted this into 1944.06.10. (year/month/day is the format for dates in Hungary)


when I press the refresh button, everything goes to <Mod Edit>, even if I manually set the column data type to date and saved it before. (Note that because of the Power Query setting, the date columns are ALREADY in date format, but those specific cells get changed to General)

this is how the dates show up in the editor, as you can see the column data type is set to date (you can see the calendar icon on the top of the column)

This is how the dates show up in the actual worksheet

 

[Ralston18]

"every new version just has some extra lines at the bottom of the file "

What are those lines? What do they do?

= = = =

Try different date formats.

For example:

MM/DD/YYYY or MM/DD/YY

Not that you need to or have to use some other format., Just see if you can discover a format that works.

 

[Akos__]

"every new version just has some extra lines at the bottom of the file "

What are those lines? What do they do?

= = = =

Try different date formats.

For example:

MM/DD/YYYY or MM/DD/YY

Not that you need to or have to use some other format., Just see if you can discover a format that works.

The contents of the txt is kinda confidential, but it's like in every line there are various data, separated. To give you an idea, it's like you put your name, DOB, place of birth, address, height, weight in one line and then, when you import it, it all shows up in different columns, each containing one of your personal data attributes. The format of these lines haven't changed because it's exported from the same programme, that's why I don't see it useful to change up the formats, because it has been working for many many months, even years. Let's make it clear, I'm not the one using this, I'm the one they asked to look into this issue. We will soon have an office update rolling out, maybe that should clear this issue, but it seems like more of a bug than a user-made error. When they tried this on different computers with a dummy file, it worked just fine, it all seems to be doing this on the computers in our worksite. I was posting it here in hopes that maybe someone else has had this bug in the recent years.

 

[Ralston18]

You, your organization may not have changed anything. However, consider that some update did make a deliberate change and all is astray.

The reason for changing/testing the formats is to determine if any format works. Or perhaps another format works as opposed to the format that seems to have stopped working.

It is interesting that 44589 comes up incorrectly as 2022.01.01 instead of 2022.01.28.

And this:

"I tried out the method in the video, but it just made everything worse. 2022.02.18. showed up as 44610 and it converted this into 1944.06.10. (year/month/day is the format for dates in Hungary)"

My thought is that there may be some mis-configuration related to region and/or country. Globalization.

FYI:

https://support.microsoft.com/en-us...you-want-8e10019e-d5d8-47a1-ba95-db95123d273e

https://docs.microsoft.com/en-us/windows/apps/design/globalizing/use-global-ready-formats

https://answers.microsoft.com/en-us...properly/2c8c61e6-28e3-480d-a37d-d144414ce1ad

From the third link:
---------------------------------------------------------------
Convert Text to Excel Date Format

Here are a couple of articles on various techniques to convert text to date format

Convert Text To Date 2019 05 13
https://excelchamps.com/blog/text-to-date/
Dates play an important role when we need to analyze trends. And, this is one of the most important things which we need to capture in a right way.In Excel, the right format to insert a date is mm/dd/yyyy. That’s the basic date format which is used by Excel to store dates.
1. Use DATEVALUE Function To Convert a Text to Date
2. Date with Back Slashes
3. Date with Month Name
4. Date with Dots
5. Date with the Month Name and a Comma
6. Date with the Day Name
7. Date with a Day Name in the End
8. Date Having a Suffix with Day
9. Date with Space Between Day, Month and Year
10. Date with Short Month Name
11. Date Without Any Space Between Day, Month and Year

----------------------------------------------------------------

Key is to work through each step in your process and try to discover when, where, and how the date format goes wrong.