'hubstorinfo' file problem

[Irishmist]

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

[Irishmist]

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]

@Irishmist  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). 

[Ashish_Mathur]

Hi,

This technique has always worked for me - Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube.

[AllisonKennedy]

@Irishmist 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?

[Irishmist]

it has a filter line for hidden files... 

[AllisonKennedy]

@Irishmist  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. 

[Irishmist]

See below for sample file and another for when I click to see contents of the hidden hubs file

[Irishmist]

Code from advanced editor for the sample file. FNDWRR Sheet is the common named sheet in the excel files where I pull the data from (get data - file - folder).

let

Source = Excel.Workbook(Parameter1, null, true),
FNDWRR_Sheet = Source{[Item="FNDWRR",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(FNDWRR_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers"

[AllisonKennedy]

@Irishmist Ok, that code looks fine provided that each sheet is always named "FNDWRR"

What is the current value for the 'sample file' if you click that query now please? Sorry for all the questions!

[Irishmist]

Screenshot after clicking on the 'Transform Sample File' query - thanks

[parry2k]

@Irishmist paste M code using the advanced editor and will be easy to explain. 

[parry2k]

@Irishmist 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.⚡

[Irishmist]

When I connect to what filter?