Cannot merge two data sets with added column.

Question

Summary

Content

Dears,

I have two sample data sets:

Dataset1:

Dataset2:

As You can see the only difference is the second dataset has an extra column with a Type: Number and Attribute: Measure (Counter).

I'm trying to do a simple merge using a Data Flow:

In a merge the number and types of columns needs to match so I'm adding a column in Dataset1 named "Counter" which exists in Dataset2. However, no matter what type/attribute for the Counter column in Dataset2 I choose - I always get the error above.

Is there any way to set the attribute/type of the column being added in the data flow?

How can we otherwise achieve the required outcome - simply merge the two above data sets?

Best regards,

Pawel.

Pawel X Dmochowski · Answer

Hi,

In the end - I have managed it differently.

Basically, in my Dataset 2 I have set the 'Counter' column as text. Then during dataflow adding a new column works as expected. In the resulting dataset I'm changing the 'Counter' column back into number.

Not super elegant but it works for me.

Hope it helps,

Pawel.

Luke Sharma · Answer

Hi again, Pawel! Have you tried using an Aggregate step in the initial dataflow after you've added the column. You can choose whether the column is a measure or an Attribute. Please will you try that and then let me know how it goes, and i'll look into other things if it doesn't work?  You can also try adding a CAST function into the "Add Column" formula to set the datatype - e.g. CAST(*column name* As numeric) and that will set the data type for that column!