Excel sheet name after overwrite with Excel Writer Node

[scazzetta]

Hi all,
hope everybody is well!
In our team we use Knime to clean and manipulate different Excel file to uniform them, so basically our workflows start with an Excel Reader and end with an Excel Writer node; after that we use the final Excel file in Power BI. In the last few months we have been encountering something weird with the output of our Excel Writer node, meaning that, after overwriting the file Power Bi doesn’t recognize the sheet name anymore, even if we don’t change it.
As of today we have found a workaround in which we open the overwritten excel file, we double click on the name of the sheet and then everything works just fine. As you can imagine, this is not sustainable in term of not being able to automatize the update of our dashboards.
Has someone else encountered the same problem? Are we doing something wrong?
Thanks in advance to anyone who helps!

[mlauber71]

@scazzetta there have been similar reports and there is a ticket. Though not sure if this is your exact problem

Excel Writer and Metadata (and PowerBI)

Hi @ReWi, thanks for the report. I confirmed that the Excel Writer writes the following as creator (matching “Verfasser”?) to the XLSX file <dc:creator>Apache POI</dc:creator> (although not PIA, also not very helpful metadata). However, I was unable to find any absolute path to the workbook inside the XLSX file itself, so I am not sure what to look for – it also is not a CoreProperty according to POI (the Java library we use). Would it be possible to create a small sample file and send me the …

One suggestion was to use parquet files to transfer data which in my experience is more stable then Excel …

Starnge problem in reading excel saved by Knime in Power BI/Power Query - Knime version 5.3

Another workaround I found is to save the output as parquet file. This works perfectly in power BI.

[hotzm]

Hi @scazzetta,

which version of KNIME Analytics Platform are you using? This would help narrow down the problem that PowerBI has. It seems PowerBI is much more picky than Excel when it comes to reading XLSX files .

Using Parquet to ingest data into PowerBI sounds like a good alternative if you don’t need to open the exported files in Excel again and just want to process them in PowerBI.

Best,
Manuel

[scazzetta]

Hi Manuel,
thanks for the answer, I’ve seen as mlauber71 said, that an internal ticket has been opened after the 5.3 version release, the one I’m using actually.

I’ve tried with csv file (which are more easily used also by other colleagues who aren’t “technical”) and it’s working for now! I’ll keep updated with the latest news about that ticket though

Thanks again for the support
Best,
Silvia

[hotzm]

Hi again,

just to confirm that it is indeed related to the compression that PowerBI does not understand (AP-23021).

@scazzetta

after overwriting the file Power Bi doesn’t recognize the sheet name anymore, even if we don’t change it.

What is the error message that PowerBI shows?

Best,
Manuel