Integrating data from various csv files with different rows into ONE structured data set with

Hi There,
I have hundreds of files from an OCR-system with an identical label set, but of course each csv file from a document looks different:

Filename label value
ABC Adress Street1 - City1
ABC Supplier name Supplier A
ABC Date 01.12.22
ABC Article_1 34 mm
ABC Article_2 31 mm
ABC Article_3 36 mm
ABC Article_4 44 mm
ABC Article_5 133 mm
ABC Article_6 54 mm
ABC Comment Pay immediate
ABC Invoice-Nr A_8876
Filename label value
DEF Adress Street2 - City2
DEF Supplier name Supplier B
DEF Date 01.10.21
DEF Article_1 12 mm
DEF Article_1 18 mm
DEF Article_2 71 mm
DEF Article_3 66 mm
DEF Invoice-Nr B_9872
Filename label value
GHI Adress Street3- City3
GHI Supplier name Supplier C
GHI Date 13.04.20
GHI Article_2 71 mm
GHI Invoice-Nr C_4562
GHI Article_3 66 mm
GHI Article_5 333 mm
GHI Article_6 11 mm
GHI Comment No Pay

I need a workflow that structures the data from all the different files into ONE transposed EXCEL-File, like this:

Filename Adress Supplier name Date Article_1 Article_1 Article_2 Article_3 Article_4 Article_5 Article_6 Comment Invoice-Nr
ABC Street1 - City1 Supplier A 01.12.22 34 mm 31 mm 36 mm 44 mm 133 mm 54 mm Pay immediate A_8876
DEF Street2 - City2 Supplier B 01.10.21 12 mm 18 mm 71 mm 66 mm B_9872
GHI Street3- City3 Supplier C 13.04.20 71 mm 66 mm 333 mm 11 mm No Pay C_4562

Unfortunately I still need XLS to upload it into a power-BI-App.

Can you please help? Thanks so much in Advance!
Best regards, Christian

The Pivot node is your friend here. Use Filename as the Group Column, label as Pivot, and “First” aggregation on the value column. If you do this on all files, you can concatenate the results and end up with the table you have at the end.
Kind regards,


Thank you so much! You made my day:-)

1 Like

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