How to remove duplicate with if else condition

[Anonymous]

Hi all,

Want to ask how can I remove duplicate with if else condition? Do it have some formula for that?

Current data

Data that I want it to be view in powerbi

I try to remove duplicate the usage (unit) but there will be a possibility that the usage (unit) have same value on different day. (in this case 9 & 10).

So my idea is = if production order have duplicate value then remove duplicate usage (unit)

Any help?

Regards,

Nuha

[stenford23]

Can you explain more which rows you expect to drop or keep and why?

[Anonymous]

Hi @stenford23 ,

I want to drop the yellow highlighted row and remain the not highlighted row.

Because as you can see in the red highlighted data is different.

Regards,

Nuha

[stenford23]

Table.Distinct(
    Table.Buffer(
		Table.Sort(
           original_table,
		   {"End Time", Order.Ascending}
		)
	), {"Usage (Unit)}"
)

Sort by End date (add start date also if you need it), Buffer it (otherwise it won't work) and get distinct.

[Anonymous]

Hi @stenford23 

I should try this method/code in advanced editor or how?

Regards,

Nuha

[stenford23]

Yes @Anonymous  insert this code in advanced editor

replace original_table with previous step name

[Anonymous]

Hi @stenford23 ,

Sorry I dont quite understand. Please refer as below for my advanced editor

Regards,

Nuha

[stenford23]

Replace the code i give to you with

drop_duplicates = Table.Distinct(
    Table.Buffer(
		Table.Sort(
           #"Expanded SAP__PPT_EV_ProductionOrderConfirmationAdd (3)",
		   {"End Time", Order.Ascending}
		)
	), {"Usage (Unit)}"
)

in 
   drop_duplicates

Change also what comes after "in" by replacing it with drop_duplicates (as in the code above)