I am trying to utilize some of the great topics in the Power Query course, but I have run across a scenario that I'm hoping can be resolved in a different way.
I will have a weekly file that is produced from a system that outputs the data in Excel 97-2003 format. The very last field on one of the sheets I need to work with is a long text - or memo field.
If I use the techniques in PQ 3.02, the last column is not displayed when I expand the fields. If I open the original file, and set the data type to Text for that column, it does recognize it.
I am trying to simplify my weekly process to opening one file and refreshing my queries - but I can't seem to find a way to recognize this field in PQ, without first opening, converting the memo field, and saving the new file before going into PQ.
Thoughts? Has anyone else run into this issue and found a solution inside PQ for this scenario?
Thanks - Beth
Hi Beth,
Have you tried saving the file as a .xlsx to see if that makes any difference?
Are you able to share a copy of the file, anonymized if necessary?
Mynda
Thanks Mynda.
Perhaps something was held in memory, or hidden from my view (I am new, and learning my way around!), but it appears to work now with a modified approach to your suggestion. (I use the .xlsx file name in downstream processes, so making that change was a domino effect I wanted to avoid.)
After only opening and saving my spreadsheet (even saving it back as the same file type), then deleting the offending query and starting over, I now see the memo field!
While this isn't the exact solution I was looking for - I hoped to just be able to rename the file - opening it, and just saving it as the filename I want to use seems to work.
I really do appreciate your quick review and response to all my queries!
I love MyOnlineTrainingHub!!!
Thanks for your kind words! Glad it's working now 🙂
