Except Unique Assert Transformation in Dataflow

Vaibhav 105

I am trying to use Asset transformation in dataflow to check the dups in source. I am not able to get the correct result when using parameters that will have single or multiple columns as primary keys (example shared below) when using below expression.

Expression : byNames(split($primary_key,','))
1.$primary_key = 'orderid'
2. $primary_key = 'orderid,lineid'
User's image

User's image

When I input single column and without split in expression then its work fine.
Expression: byName($primary_key)

User's image

Kindly suggest the correct way to write the expression when using multiple columns.

Smaran Thoomu 11,370 Reputation points Microsoft Vendor

2024-05-27T09:11:47.66+00:00

Hi @Vaibhav

Thanks for the question and using MS Q&A platform.

Based on your query, it seems like you are trying to use the Assert transformation in dataflow to check for duplicates in the source. However, you are not getting the correct result when using parameters that have single or multiple columns as primary keys. You have shared an example where you are using the expression byNames(split($primary_key,',')) and when you input a single column without split in the expression, it works fine.

To suggest the correct way to write the expression when using multiple columns, you can modify the expression as follows: byNames(split('$primary_key',','))

Here, you need to enclose the $primary_key parameter in single quotes to ensure that it is treated as a string. This expression will work for both single and multiple columns as primary keys.

Additionally, you can refer on Assert transformation in dataflow for more information and examples on using this transformation.

I hope this helps! Let me know if you have any further questions.
Vaibhav 105 Reputation points

2024-05-27T19:10:09.6333333+00:00

@Smaran Thoomu
Thanks for the response.

byNames(split('$primary_key',',')) --> This didn't work.

Can you share the screenshots so that I can see where I missed.

Just to add,
toString(byNames(split('$primary_key',','))) is working if I am giving single column name in parameter $primary_key (Ex - orderid). However, as soon as I give two column name ('orderid,lineid') , I get below error:

"Function 'toString' argument should be StringType but was ShortType"

NOTE - Both my columns are Int type.

Thanks
Smaran Thoomu 11,370 Reputation points Microsoft Vendor

2024-05-28T16:11:19.7233333+00:00

Hi @Vaibhav

Thanks for the clarification and additional details. It appears the previous suggestion with enclosing $primary_key in single quotes might not be the solution for your specific scenario where columns are integers.

To address the error, you are facing when using two column names ('orderid,lineid'), you can modify the expression as follows: toString(byNames(split('$primary_key',',')), 'UTF-8')

This expression will convert the output of the byNames function to a string using the UTF-8 encoding. This should resolve the error you are facing when using two column names.

I hope this helps! Let me know if you have any further questions.
Vaibhav 105 Reputation points

2024-05-28T20:11:05.1833333+00:00

@Smaran Thoomu

Thanks for the response.

I am still getting incorrect result. I will really appreciate if you could test the solution at your end and share the screenshots so that I can make changes accordingly.

As you can see in the below screenshot , I am getting all 4 as invalid rows when using 'orderid,lineid' in parameter. Instead, I should get all 4 as valid rows because there are no dups against orderid,lineid.
Smaran Thoomu 11,370 Reputation points Microsoft Vendor

2024-05-31T07:39:10.81+00:00
Hi @Vaibhav

I understand that you are still facing issues with the Assert transformation in dataflow.
Based on the screenshot you shared, it seems like the Assert transformation is not working as expected when using multiple columns as primary keys. To further investigate this issue, I would recommend checking the following:

Ensure that the data types of the columns used as primary keys are the same. In your case, both columns are of type Int, which is correct.

Check if there are any leading or trailing spaces in the column names used as primary keys. This can cause issues with the split function used in the expression.

Try using the byName function instead of the byNames function when using multiple columns as primary keys. The byName function should work for multiple columns as well.

I hope this helps! Let me know if you have any further questions or if this resolves the issue.

Share via

Except Unique Assert Transformation in Dataflow