The Excel data type altered automatically during pivot operations.
The Excel data type altered automatically during pivot operations.
Hey!
I'm dealing with an Excel datasheet that uses PowerPivot to import from a plain text file. One column contains dates. I've adjusted the date format in the Pivot editor, but whenever new entries are added—same as before—they still show up as text. Even after clearing the worksheet and refreshing, old dates remain in text mode. The Pivot syntax hasn't changed.
Has anyone faced this issue? If yes, what did they do to fix it? I've tried manually picking the date column and changing its data type to date, but it only works until a new file entry comes in or I clear and refresh.
It seems unclear about the import method you're using and the structure of your Excel spreadsheet. Generally, you might just need to apply the relevant function. Examples (though not necessarily perfect fits) could include the links provided. Make sure to save all original data before testing. It's wise to create a separate test environment to see which conversion approach suits you best.
Let me share the background you provided. The text file includes dates in a specific format. In PowerQuery, by default, columns with these values are text. When editing the query, the data type was changed to "date". This setup functioned correctly for some time, producing results like 2022.01.01. However, after several months without modifications to either the file or the editor, the date displayed as 44589 instead of the expected 2022.01.28. It's unclear why this discrepancy occurred despite no changes being made.
Check if the last text file that functioned properly is the same as the one being used or processed when errors started.
First, ensure the imported data isn’t the root cause of the issue.
How is the original text (source) file generated?
FYI:
https://www.contextures.com/exceldatesfixformat.html
The page offers tips that can aid in resolving the problem.
Remember: set up a test environment and use real data copies for testing.
The source txt file comes from an SAP program and maintains a consistent structure, with only minor additions at the bottom in each new version. It imports without issues, as confirmed by the screenshot. I routinely set up a test environment and create copies of the original files for testing.
I attempted to use the method demonstrated in the video, but it actually made things worse. On 2022.02.18, it displayed 44610 and then converted it to 1944.06.10 (date format used in Hungary). When I refreshed the page, everything redirected to <Mod Edit>, even though I had manually set the column data type to date and saved it earlier. (It's worth noting that due to the Power Query configuration, the date columns are already in the correct format, but certain cells were changed to General.)
This is the display of dates in the editor, as you can observe the column data type remains set to date (you’ll see the calendar icon at the top of the column).
The actual worksheet presentation also shows this behavior.
What are those additional lines? What function do they serve?
Consider trying various date formats.
For instance, you might try MM/DD/YY or YYYY/MM/DD.
These changes don’t require any special format—just experiment to find one that works.
The information in the text is somewhat confidential, but each line contains different data points. To illustrate, it appears you list personal details such as name, date of birth, place of birth, address, height, and weight on a single line, which then gets displayed in various columns when imported. The format remains consistent because it was exported from the same system, so altering it wouldn't be practical given its reliability over many months or years. It's important to note that I'm not the one operating this system; I was contacted about investigating this matter. We are preparing an office update that might resolve the issue, but it seems more like a technical glitch than a user error. Testing on other computers with a placeholder file showed it worked perfectly, suggesting the problem is likely with our current setup. I hope this information reaches someone else who may have encountered this issue recently.
You may not have altered anything in your organization.
Yet, it’s worth noting that some updates might have introduced a deliberate adjustment, and everything seems off.
The purpose of altering or testing formats is to see if any particular format functions correctly.
Or maybe another format could be more effective than the one that appears to have stopped working.
It’s noteworthy that 44589 is displayed incorrectly as 2022.01.01 instead of 2022.01.28.
And this:
"I attempted the method in the video, but it only made things worse. 2022.02.18 appeared as 44610 and then converted it into 1944.06.10. (the year/month/day format for dates in Hungary)"
My belief is that there could be a misconfiguration tied to region or country settings. Globalization.
FYI:
https://support.microsoft.com/en-us...yo...95123d273e
https://docs.microsoft.com/en-us/windows...dy-formats
https://answers.microsoft.com/en-us...pr...44414ce1ad