Excel Writer and Metadata (and PowerBI)

ReWi · August 1, 2024, 6:28am

Hi there,

we use KNIME to create Excel-Files that were used from other colleagues as Input for PowerBI. Now I got complaints from these colleagues that PowerBI stopped processing of these files with an error message: “The key didn’t match any rows in the table”.
I’v checked the KNIME generated Excel files and firstly didn’t find any problem. After some research I found hints to check files in Excel itself (sorry for the german messages):

After removing these metadata (author, absolute path) PowerBI works again.

I see two solutions:

Found the switch in PowerBI to ignore these metadata
Avoid writing of these metadata in KNIME Excel Writer

Option 2 is a topic for this forum.
Any ideas? Any similar experiences?

Thanks a lot!

Regards,
ReWi

mlauber71 · August 1, 2024, 6:56am

@ReWi you could try and remove these entries with the help of openpyxl which is part of the KNIME python extension.

hotzm · August 2, 2024, 1:16pm

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 versions before and after cleaning?

For now, I opened an internal ticket (AP-23036) on your behalf to not even write the info if they make problems processing the file.

Best,
Manuel

system · August 13, 2024, 6:23am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

ReWi · August 13, 2024, 8:49am

@mlauber71 : Thanks a lot for the hint.
Even I was only able to remove the author entry the result was enough for PowerBI.

ReWi · August 13, 2024, 9:08am

Hi @hotzm:
thanks a lot for reopening the ticket (also for the kind mail contact).
Yes, “Verfasser”=creator (or author). I also didn’t find any entry regarding “absolute path”.

Regards,
ReWi

mlauber71 · August 13, 2024, 9:29am

@ReWi glad it worked. Although it also should work for other properties as well: