SMB file share problems within Power BI on-prem gateway

[Anonymous]

Hi all, 
I'm not sure if this topic has been posted about before but nevertheless I hope to receive some help on it from the community. Please refer me to other discussions/threads if someone has resolved this problem earlier.

Anyway, a little background: A few weeks ago I added a network drive (SMB file share) to my local computer. I want to use an Excel file in a folder on that drive in a Power BI report I'm developing for one of my clients. In the future I'll be wanting to connect to several similar spreadsheets, so I decide to use the Folder connector in Power BI Desktop to get data. I also decide to use a Power Query parameter for the file server name because I figure out that PBI Desktop needs the full path including IP address to be able to connect (\\IP\server\share), while I should skip the IP address when creating the data source to use with the gateway in Power BI Service (\\server\share). Otherwise I get an IO error.

So far so good. The problem arises when I want to use scheduled refresh to keep my data up to date. The refresh keeps failing every morning, giving me an error message similar to this:

Some simple investigation in Power Query online (Power BI dataflows) sheds further light on the issue. The folder I'm trying to reach seems to be empty, but I know it's not because I have no trouble finding the files in the Desktop application, my File Explorer or my browser.  Anyone else run into similar issues before? If so, how did you solve it?

Grateful for all attempts to help as always 🙂 Below are some screenshots of what I'm seeing in Power Query online and in PBI Desktop. Please excuse the mixture of languages, English is not my native tongue.



 

[GilbertQ]

Hi there

From the error message it is rather that the table appears to be empty.

From your second screenshot it is finding the file but the contents are blank.

Can you confirm that the Excel spreadsheet does have the data in a table?
And that there are contents in the table?

[Anonymous]

Hello @GilbertQ,

Thank you for your answer! The Excel spreadsheet does not have the data in an Excel table, but rather in spreadsheet format (e.g., in columns and rows but not defined as a table by my client). In the screenshot below you can see that there is content in the Excel file, though. Please excuse the formatting.

Excel spreadsheet

I agree with you that the table appears to be empty from the error message, but I suspect that is because of the way my queries are set up. I'll try to explain what I mean. In Power BI Desktop, I first define a query that connects to the network folder I need. Then I choose the correct Excel file, which consists of several spreadsheets, which Power Query has no problems finding. After that I reference the first query as many times as I need to separate the data in the sheets into different fact tables in my data model (and do the necessary transformations, etc. before loading the data into PBI Desktop). All the data is still there, as evidenced by the screenshots below.

PBI Desktop query 1, part 1PBI Desktop query 1, part 2PBI Desktop query 2 (referenced from query 1)

The problem arises in the Service. As I stated before, if I try to replicate my work in a Power BI dataflow, the engine finds the folder, but no files in the folder. In other words, that table is empty, but I know it shouldn't be. Any ideas? 

[GilbertQ]

Hi there

Thanks for all the details, it would look like there might be a mismatch between the two

What happens if you copy and paste your M Code from Power Query into the dataflow?

[Anonymous]

Hello again @GilbertQ,

No problem 🙂 If I copy and paste my M code into a new dataflow without making any changes to the current value of my FileServer parameter I am not able to access the folder. I'm told a gateway is required to connect (which I have already configured) but both my Windows credentials (on the form username@company.com) and folder credentials (domain\username) are deemed invalid.

Also, if I try to add a folder data source with a path like \\IP address\server\share to the gateway I get the error message below. The only way to resolve it is by removing the IP from the path in Power BI Service. 

Error message from Power BI gateway

Again, thank you very much for all your help! It seems we are located in very different time zones so I am doubly grateful for you taking time out of your busy day to help me 🙂

[Anonymous]

Btw @GilbertQ ,

Interestingly on demand refresh (publishing from Power BI Desktop) seems to be working fine even though I get the same error message in Power BI Service. I have developed and saved my data model in one .pbix file and then so far I have two more reports which connect to my Power BI data set using the "Connect Live" option in Desktop. I've made some changes in my data model today and all these changes are reflected in my two other reports. Power BI Service only returns a "danger sign" to me when I try to re-publish my data model report, though (after I change the value of my FileServer parameter as well). Could this have something to do with the difference between scheduled refresh and OneDrive refresh?

Thanks again! 

Workspace in Power BI ServiceRefresh history, scheduled refreshRefresh history, OneDrive refresh

[GilbertQ]

It could possibly have to do with the OneDrive and scheduled refresh being different