File contains corrupted data with $_xlnm#_FilterDatabase

[TK12345]

Hi all, 

I am trying to use sharepoint folder/file/content as input for my PBIX. 

By selecting one excel from my sharepoint list it works fine and it will load. Besides that I have a folder with 80 excelf files I want to combine, in Power BI desktop there is no error by expanding the binary it creates helper queries and I can make my transformations after and it all works. When I publis to Power BI Service, I get the error "File contains corrupted data". While testing I found out it is because of the "Specificatie$_xlnm#_FilterDatabase" when I have 2 files and expand the binary I get Specificatie_xlnm#_FilterDatabase without the dollar sign and I can get thte 2 files loaded. 

When doing to all my files, I get the $ in front, even without using that one, it still gives me the error in the service.

How can i connect to my folder, and use those 80 files with a helper query without getting that $ issue because than the file is corrupted... 

Who can help me out?

[ibarrau · Accepted solution]

Hi. Consider that when you combine files there is a lot of code generated automatically. A new folder is created where the code of this combination is built. In order to work with the transformation of every file before gathering all together, you could change code there. You might need some power query skills. Check this out:

There you can find a "Transform File" with an Fx symbol. That is the function that will run for each file. Check the transformations. If there is something there you can do to prevent the "$" like make the column text, prevent convert to number or replace values. You could work in there. To make it easier to understand, you can test drive your changes with "Transform Sample File". If I'm correctly you can modify that one and nothing should change. That table is an example.

I hope that helps,