Merge two datasets without common column in Azure Data Factory

Question

I have two datasets where I need to do a join/merge in Azure Data Factory, but without having a common identity column. This might be my an oversight from my side, as it should be a very trivial task to do, but I cannot seem to do it via a join or a union.

One dataset only has a couple of rows with a "name" column, let's say rows A, B, C whereas the other have thousands (1-N).

For each row in the large dataset I want A, B, C rows, so it effectively becomes:

1A 1B 1C 2A 2B 2C ...

Any help is appreciated,

Thank you.

Answer 1

You can use Custom (cross) join type in the Join to get the result in this case.

Follow the demonstration below:

Sample Large Dataset(Numbers) with numbers up to 15.

Small Dataset(Letters)

Now, use Join with Large dataset as left and small dataset as right and use custom join with the condition as true().

In the Optimize of Join, select off at the Broadcast to get the above format of the data.

You can see the merge of two datasets below.

If you want the above in a single column with values like 1A,1B,1C..., first use the derived column to concat the above values and then select any column using select.

Derived Column

Now use select to select any column above.

Output