Hello. I'm using Excel 2019, and having an issue with some data.
Say I have a PDF file with the following set of values:
2012-19-08
2015-15-03
2015-23-04
2016-04-11
2016-08-12
2017-12-02
These are not dates; they are unique ID numbers for different items.
I want to get these values into Excel (and into Power Query). I use Acrobat's export function to export the PDF file to an Excel spreadsheet. In the newly-created spreadsheet, any value for which the middle number is <13 (a valid number for months) has been automatically converted to a date; the format of the values displays the original value, but in the formula bar the actual value has become a date (for example, 2016-04-11 becomes 4/11/2016).
I have been struggling with how to get these values converted back to text. I can get it done, but not in a way that is truly usable in a query; I can simply open up the spreadsheet and manipulate the values in various ways to get them converted to text.
For example, I can use an =TEXT formula, copy, then Paste Values. Or I can copy the values, paste them into a .txt file, then copy and paste them back. And so on. But what I need is to get the values imported into a query, and have Power Query do the conversion every time.
I have played around with the Date.ToText function in Power Query. For the values which were converted to dates, I can get them converted to text successfully; but of course the values which are NOT dates then throw an error, because Date.ToText is trying to convert non-date values.
I've also tried New Column from Example, but haven't had much luck with that.
I would appreciate any help on this! Thanks!
