0

I am using Azure Synapse Link to connect Azure storage to load the RAW to from CRM. there is option which i choose Append mode only. it is creating a duplicate records if anything changes happen from CRM tables(modules). in that case, how do we handle this scenario, either aggerating in dataflow to remove the duplicate records or else we can handle in dataverse(power apps) itself. kindly advise. eg :

accountnumber    accountname
222               XXX
222               XXX
222               XXX

how do we handle in dataverse side or else dataflow aggregation flows. kindly help me.

links referred : https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-advanced-configuration

also i could find in ms docs for below snippet code but where and how do we use, if some please share the screenshot below code.

aggregate(groupBy(mycols = sha2(256,columns())),
    each(match(true()), $$ = first($$))) ~> DistinctRows

as per below link : https://learn.microsoft.com/en-us/azure/data-factory/data-flow-script#distinct-row-using-all-columns

Developer KE
  • 71
  • 1
  • 2
  • 14

1 Answers1

0

I have tried to repro this in my azure ADF dataflow environment.

  • Sample source is taken as in below image. enter image description here

  • Select the Aggregate transform next to source.

enter image description here

  • In Aggregate Settings, Click group by and then click Open expression builder enter image description here

  • Enter the Column name and enter the expression as sha2(256,columns()) and then click Save and Finish enter image description here

  • Then, in aggregate settings, click on the aggregates and then click the Open expression builder enter image description here

  • Click on + Add column pattern near column1 and then delete Column1. Then Enter true() in matching condition. Then click on undefined column expression and enter $$ in column name expression and first($$) in value expression. Then click Save and Finish. gif11

Output Of Aggregate Transform enter image description here

This can be done by writing a script in dataflow. To write a dataflow transformation script , Click the script button enter image description here

The script for equivalent UI transformation is shown in below image.

enter image description here

By this way, you can aggregate in dataflow and remove the duplicates.

Aswin
  • 4,090
  • 2
  • 4
  • 16