Forum Discussion

RWeaverAMT's avatar
RWeaverAMT
New Member
3 years ago

Convert Date to Text in Power Query

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!

1 Reply