Log In
Register
Home
Active Member
September 20, 2020
Offline
I have a strange excel/html hybrid type file that is exported from a program.
If I open this file in Excel, it does warning about the format, but after clicking okay, the file displays normally.
The real problem occurs if I try to use Power Query to start a new query from external workbook or from folder.
It seems I used to be able to tell Power Query to open the file as html, but that option no longer seems to be available. I can try to import file from the Web, and give it the file path to my file on my local computer, but again, it does not work because the f"ile is not in the expected format"
I can change the file extension to html and that does work, but I would rather automate the process of working on these files when I deal with them.
As a kludge/workaround, I can use get file from folder, transform data, and then when I have my file list I can create a custom column and use:
=Web.Page([content} to get a new column that can then be expanded and from there further expand and get the data, but this would rather not use this if possible.
Where did the option go to tell Power Query how to attempt to open the file as a different type? Or am I completely losing my mind and that was never there?
November 8, 2013
Offline
Hi Mike,
When you have the list of items in that folder, the first column is Content, with data in Binary format. If you right click any cell from Content column, Add as a new query, a new query will obviously be added. Right click on that binary object from editor and you'll be able to select the type of file.
But: it will do exactly what you did:
if you select HTML, PQ will add a formula : Web.Page([Content])
if you select Excel Workbook, PQ will add a formula : Excel.Workbook([Content])
