Forum Discussion
'hubstorinfo' file problem
I load my excel files from a folder as they are similar. This has worked through several refreshes but now P-Bi won't refresh - initially looked like it was 64 or 32 bit problem - but I have 32 bit now for bothe access & PBi and just different erro message.
So I tried loading each of the 3 excel files separately and they load; But I still need load from file folder to work for future changes.
It looks like problem is a hidden file I cannot remove - does not show up when I select 'hidden names' so cannot delete. I copied the files to a new folder and tried uploading but same error..
An error occurred in the ‘Transform File’ query. DataFormat.Error: External table is not in the expected format.
Details:
.hubstorinfo
13 Replies
- parry2k
Super User
Anonymous when you connect to the filter, you should filter the list of files on IsHidden before further transforming the data, it will remove hidden files from the list and everything should work as expected.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- Ashish_Mathur
Hi,
This technique has always worked for me - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube.
- parry2k
Anonymous paste M code using the advanced editor and will be easy to explain.
- AllisonKennedy
Community Champion
Anonymous As Parry mentioned, you can tell Power BI to ignore hidden files.
When you do Get Data > From File/Folder in Power BI, it should automatically generate the filter to remove hidden files.
What does the 'source' step look like for your broken query? Do you see a row for .hubstorinfo in the table?
Can you click the Advanced Editor tab of your table query that's broken and paste here please?
- AllisonKennedy
Anonymous Thanks for the screenshot - looks like the error might be happening even earlier than we thought. Can you please select the 'Transform Sample File' query that has the error symbol next to it and see what's happening there? You might see a 'Go to Error' button. If you do, please click it, then take a screenshot and also select the previous step and take another screenshot. Post both here if you still haven't solved by then.
Note that I have a workaround that works but I keep having to repeat it when this error reoccurs... I create a new source folder, copy the excel files to it (leaving the hidden file I cannot see hubs.. behind) and then in PBi select drop arrow for Transform and select change Data source Settings, then Change Source to new folder. I've repeated this 3 times over past 2 days. So problem could be solved if I could find a away to eliminate the hidden file that appears from nowhere.
- AllisonKennedy
Anonymous Two things to try please:
A) change the 'Source' step for the parameter1 to use a specific file (Not the first file as it's currently doing).
B) In the OracleOrigData1 step, click the 'Filtered Hidden' step and see if the .hubstorinfo file is still in the list. Add a filter here to filter for filetype = .xlsx or .csv (whichever your filetype is).
B should fix it by itself without needing to do A).
