Budget table Issue - duplicate amount instead of update

[fabiolamelo]

Hi experts,

I am working in one financial project, that there is a table called budget and the amount of the budget could be change anytime.

I am struggling with refreshing data, because when the "budget amount" changed, the power bi understand that is a new transaction and not a update of the collumn. As you can see in the following example:

Budget table

CODE |COMPANY NAME |CODE                     |AMOUNT |COMP_CODE|DOC_NO |TRAN_DATE |TRAN_DESC
FABI   |FABI NAME           |BUDGET.FABI.2016 |369          |XXX                 |XXXX      |01-Jul-16      |Period Balance budget transaction

Alteration the budget instead of 369 is 400. The power bi understand 2 different transaction instead of just update the amount.

CODE |COMPANY NAME |CODE                     |AMOUNT |COMP_CODE  |DOC_NO|TRAN_DATE |TRAN_DESC

FABI   |FABI NAME           |BUDGET.FABI.2016 |369          |XXX                 |XXXX      |01-Jul-16      |Period Balance budget transaction
FABI   |FABI NAME           |BUDGET.FABI.2016 |400          |XXX                 |XXXX      |01-Jul-16      |Period Balance budget transaction

Note: The only collumn that was changed is "Amount" 

Also,I`m using the API to get the data and unfortunatelly I can`t share it because is a confidential data.

Is there is any solution that I can use to the power bi just update the amount without create a new row?

Thanks 

Fabi Melo

[amitchandak · Accepted solution]

@fabiolamelo , Ideally power bi refresh should overwrite the data. I think you are getting two rows from the source.

you can refer to this blog , if this can help

https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/

[fabiolamelo]

Thanks @amitchandak