Is there any transformation to deal with joined cells?
What do you recommend to transform 2 sheets like that in a database format? i.e.
Date; Division; Actual value ; Target Value;
or
Date; Division; Actual /Target; Value;
The proposed solution is not very straight forward but does the job. Should work for any number of dates, divisions. Will need a modification if there will be other categories besides Actual/Target.
UPDATED
The approach here is to get column names (by unpivoting one row), then calculate new column names with correct dates (here the “Shift” transformation comes in handy) and then rename columns in the main table using the “Rename with lookup” transformation. After that then main table can be unpivoted as usually.
Here is another solution. It’s a little shorter than the previous one, but it will work correctly only when all the Actual and Target values are numbers. Also this solution includes transformation from Date; Division; Actual /Target; Value; format to Date; Division; Actual value; Target Value; format.
Solution unpivot all the columns except [Division]. Then it fills down dates and Actual/Target “flags” and filters out redundant rows.
Transformation to Date; Division; Actual value; Target Value; format is made by splitting data into two separate tables with actual and target values and merging those tables by Division and Date column.