Good evening,
I have recently migrated to version 5.3 of analytics platform and noticed the following problem, very very strange.
If I save an excel data from Knime, I get an error in power BI when I try to use it as source model. If I enter the excel file manually and simply save it again, the problem do not occur.
I have no explanation for this. I have never experienced this issue before with previous version and I guess it is a bug.
Can someone help me? See below. Thanks in advance.
Has anybody encountered such problem? The only solution that I have found till now is to set the excel file to automatically open after the saving, so that I can save it again manually.
I have tried with several files and all the excel files that I save with the version 5.3. are not accepted in power query/power bi as they are detected with a dataformat error.
Can someone suggest me how can advise Knime on possible bugs?
Thanks in advance,
Stefano
Hi @vstefano,
does the file exactly contain the data that the screenshot shows? Then I can compare files generated by 5.2 and 5.3. Unfortunately, I don’t have access to PowerBI to experiment with it.
In general it seems that PowerBI is a bit picky when it comes to reading Excel files. Excel itself is very lenient and will silently correct many internal things, such as setting the optional sheet dimension attribute. I think this attribute could be what PowerBI complains about, since otherwise your data looks very simple.
Best,
Manuel
I tried to reproduce it using the “Desktop” Power Query in Excel itself, and the file I generated with KNIME 5.3 imported without problems. I used only the data you showed as contents. Would it be possible to attach the sample file that is working and the one that is not working, so I can compare them in depth?
PBI is very picky about decimal points and/or commas.
Maybe there is a difference in the way 5.3 Excel Writer is saving the data compared to your local settings for Excel which is causing PBI to not understand that it is reading a decimal?
PBI maybe interpreting the point as a 1000 separator instead…
Just a guess - good luck finding a solution
not power BI desktop… the error occurs in power bi service. Here attached two sample file. One is working and the other not.
from Knime not working.xlsx (9.0 KB)
after saving working.xlsx (14.0 KB)
I have attached two samples file, one working and one not. The problem does not occurs in power bi desktop. It occurs in power bi service hence breaking the scheduled refresh etc…
@vstefano what you could try is use openpyxl to export Excel files and see if they are more stable
Thanks for providing the files. I think I may have found the problem. After an update of the underlying library we use for Excel file handling (Apache POI) the Excel Writer uses a changed default for file compression now (link to change), which was not documented in the changelog… I have captured this in our internal ticket AP-23021 to get it fixed. It seems that PowerBI’s XLSX parser does not handle that compression type, whereas Excel’s does. So opening the file in Excel and saving it again, silently changes the compression type to the one that PowerBI wants.
To solve your problem, you could try the following workaround, which triggers the Excel Writer to use a different POI class to write sheet data, thus using a different compression type:
In case the file already exists but you want to overwrite it completely, and not just the single sheet, you would need to delete it beforehand with the Delete Files/Folders node.
This workaround could work since your data seems to be small and contains no formulas that would accidentally be evaluated.
I would be glad if you could tell me if this is a suitable workaround until we release a proper fix.
Best,
Manuel
Hi Manuel,
thanks for the input and for opening the ticket. Regarding the proposed approach, I am already using this (append the file and overwrite the sheet). I have tried to change it to “overwrite file” but same results in one case or another.
I have tried to use the Delete files/Folders node and then recreate it but nothing.
I guess the problem is strictly related to the way Knime saves the file.
At the moment I am using the workaround of authomatically open the file (setting the excel node) after the saving. Once opened I save it manually and it is done. Fortunatly at the moment I do not have a lot of files.
Hopefully the ticket can solve the issue.
Stefano
Hm, just to confirm, you did check the “Evaluate formulas” option and it is enabled in the dialog?
If this option is not used, because it is unchecked or disabled (it is disabled if “Overwrite” the whole file is selected), it is not triggering the workaround.
If the file should only contain this one sheet, you don’t necessarily have to delete the whole file beforehand. We just need to “trick” the Writer node to enable and use the “Evaluate formulas” option so the xlsx file is written the way PowerBI wants.
Hi Manuel,
thanks for the support.
I have done some text with the flag “Evaluate formula” (which is default if you overwrite the file) and here the results in the different modalities (overwrite or update):
Stefano
Thanks for the report what works!
Hi hotzm,
just for information.
I have the same issue with the way the excel writer node creates the files.
I hope a fix for this issue will be provided asap.
Until then, i will try the workaround provided in this thread.
Another workaround I found is to save the output as parquet file. This works perfectly in power BI.
