Transpose Dataset with Double Header

[Henan]

Dear KNIME community,

I’m currently struggling to prepare a dataset for visualization in Power BI. The dataset contains a negligible first row (1) and a double header (row 2 & 3). I’m trying to transform the data so that later I can create a line chart in Power BI that shows the development for all companies for all KPIs (column A) over time. The data currently looks like that:

I was trying to transpose the data like this, but couldn’t make it work:

Does anyone have an idea on how to approach this task?

Best,
Henan

Example.xlsx (13.0 KB)

[bruno29a]

Hi @Henan , can you show us what is the desired data output?. How should the data look like?

EDIT: Nevermind, I mistook your second screenshot as the results that Knime produced, but I understand that it’s actually what you want as results, is that correct?

How did you do this transformation in Excel? The steps can usually be reproduced in Knime

[gonhaddock]

Hello @Henan
I haven’t tested yet with your data but, as all your source data formats are Double in all the columns but the first one. It can be very simple.

Read the data skipping the first row [ ‘Financial Data’ 1:1 ], and apply the ‘Transpose’ node; you only will need to insert column headers from the resulting first row afterwards.

Transpose

BR

PS.- The resulting data configuration out from the Transpose, can be quite suitable to work out in PBI as well

[Henan]

Hi @bruno29a , you’re right, the 2nd picture shows the desired output. I just manually cut & pasted the data in Excel into the format shown in the screenshot. Unfortunately, I couldn’t replicate it in Knime

[Henan]

@gonhaddock I’ve tried the Transpose node, but it doens’t exactly look like the desired output in the 2nd picture of my initial post.

Here’s the result:

[gonhaddock]

Hello,
Try to import data starting from Row2 in the Excel reader configuration. In this way now, you are in a similar point that when you started.

@gonhaddock

Read the data skipping the first row [ ‘Financial Data’ 1:1 ]

I’ll run a test…

These are the basics but is not going to work because the custom format of your negatives. As we have to read them as string. Give me few minutes and i’ll deliver a functional workflow…

[bruno29a]

You probably need a combination of transpose and pivot

[gonhaddock]

@Henan
The main problem looking at your excel (thing that I usually try to avoid ) is that; when I saw the picure first, I though all the data was numeric but the Example file provided is cheating us. As in the ‘Refunds’ row there aren’t numbers in the source; they are typed in text format with parenthesis.

However in the second tab “Transformation Idea”, ‘Refunds’ is numeric and positive (?)

And also but not less important, your file has been saved from a system settled with Spanish formatting for numbers, then Refunds’ strings consequently are typed with comma

@gonhaddock

as all your source data formats are Double in all the columns but the first one.

I was wrong because in one of the rows (Refunds) the values are strings, and I won’t modify your source.

This is the workflow, and as you see, the quality of the data can force you to complex transformations.

Proposed PBI Structure:

Requested Transformation Idea

20220203_matrix transpose2.knwf (74.3 KB)

BR

[Daniel_Weikert]

@Henan
Double Header can also be handled in Power BI directly.See
Power BI double header
br

[Henan]

Thank you so much @gonhaddock for your help! Your solution works like a charm

I really appreciate all the effort and time you invested in this!