enterprise gateway refresh error

[Anonymous]

Hello - 

Trying to set up an enterprise gateway to connect to a server hosted folder of .csv files. With the new enterprise gateway, it isn't clear how to set up that connectionm. What I found to work was create a desktop file and publish to online, then add the datasource to the enterprise gateway. Once done, go to the settings of the dataset and change from personal gateway to enterprise gateway. 

The problem comes with trying to refresh the data. 

It is throwing this error:

Underlying error message:	The Csv.Document parameter 'Columns' is invalid.
Underlying error code:	-2147467259
Microsoft.Data.Mashup.ValueError.Reason:	Expression.Error
Cluster URI:	WABI-WEST-US-redirect.analysis.windows.net
Activity ID:	364c3316-5242-4ca3-9be9-f92516b80350
Request ID:	63d35696-f34b-d05a-d72b-a1774a42cbbc
Time:	2016-03-03 17:49:17Z

I tried to delete any 'columns' that were named in the Query editor and that didn't solve.

any suggestions? 

[LyndonJohnson]

We found a solution by modifying the Csv.Document step as explained below. We encountered this same error in one of our solutions built in Power Query when a client updgraded from Excel 2013 to Excel 2016. The query was importing a CSV and was returning this error on the step that uses Csv.Document. It was and is still working correctly in Excel 2013, but was not working in Excel 2016.

The code that was returning the error was:
Csv.Document([Content],[Delimiter=",", Columns=40, Encoding=1252, QuoteStyle=QuoteStyle.None])

We changed it to the following:
Csv.Document([Content],[Delimiter=",", Column=40, Encoding=1252])

There were 2 changes, highlighted in red. Columns becomes Column (i.e. singular). This then no longer returns the parameter 'Columns' is invalid error message, but then returns an error message related to QuoteStyle. We then removed the QuoteStyle part. This then worked in Excel 2016. This code also works in Excel 2013 and in Power BI Desktop.

[jwainz]

Switching to .xlsx did work. Switching to .txt didn't work. 

[elsalvador182]

I had this same issue, not sure cause or why or if its a bug..i got it working as per this thread (hopefully the fix keeps working)

http://community.powerbi.com/t5/Integrations-with-Files-and/Can-t-refresh-data-Error-message-quot-Th...

[Anonymous · Accepted solution]

Hello -

I was tinkering around and remembered that Power BI sometimes has problems with .csv files. The formatting gets all messed up and it works better designating your .csv file as a text file and letting the query dilimit the columns at each occurence of the comma. 

I changed the query to handle the files in the designated folder as text files and the error dropped. Data is successfully refreshing with manual triggers. Have a scheduled trigger in 5 minutes to see if that works, can't see why it wouldn't. 

thanks for reading. 

[Anonymous]

A little late to the party here, but how did you change the query to handle the files in the designated folder as text files? I'm not seeing how to do that.

I've been stuck on this one for a while, so I'm hoping you can help me out...

Thanks!