Positive & Negative duplicate values

[Kent_Heng]

Hi All,

Just started using Power BI, and stumbled onto this accounting issue with the data.

I have one column "Cost" and it can have negative values, which i dont not want to display, as it affects my charts and all. 

For each negative value, there will be a corresponding positive value of the exact same number (example below). This is due to my company being charged an amount, and then subsequently receiving a rebate of the full amount. But this will log as 2 rows in my database. 

I have tried to use Excel "duplicate value", but it ended up removing all other (positive values) with the same numbers, thus reducing my data. 

Not sure if there is any way i can resolve this in the Power Query Editor? Or by another other method?

Cheers!

Regards,

Kent

[Anonymous]

If you just want to remove both the positive and negative duplicate items you could add a column that takes the abs of the cost then remove duplicates from it.

To do so you would open the query editor then click add a column then Type Number.Abs(Number.From(Cost))

This will make a column that is the absolute value of the cost column. If you then right click on this column and remove duplicates and then remove the absolute cost column you will get what you want.

A word of warning, this solution will remove any items that cost the same as they will appear as duplicates, to get around this you could try selecting more columns such as the date when you remove duplicates. However if the payment and rebate occour on different months they would not be removed.

This code will demonstrate the steps described above.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQzMzYy0DMwUNJRMtQ30je0UIrViVYyMjY3M8AU1sWhXBeHemMDA0sjMws9M0uwuDFcPVaJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Cost = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cost", Currency.Type}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ABSCost", each Number.Abs(Number.From([Cost])),Currency.Type),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom", {"ABSCost", "Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"ABSCost"})
in
    #"Removed Columns"

[Kent_Heng]

Hi Thomas, Thanks for your reply. I have tried your step, by adding the custom column in Query editor. However, when i clicked ok, the new column is generated and all the numbers are "Error", with the following error message: Expression.Error: We cannot apply field access to the type Function. Details: Value=Function Key=Cost Am i missing another step prior to adding in the ABS formula?

[Anonymous]

Are you trying with the code I posted above or are you trying to apply it to your own data?

[Kent_Heng]

Hi Thomas, Following your steps in my own dataset. I did notice in your below table, where you convert the cost to currency type? Or something?

[Anonymous · Accepted solution]

There are two type conversions in my code.

One is changing the type of cost from text to currency as when you manuall enter data by default is text typed. This is to allow numeric functions to be applied later such as sum average etc.

The second transformations is inside the custom function.

Number.Abs() requires that you enter a numeric type for it to work, so to ensure that it is a numeric type going in I have wrapped the column in Number.From() which converts to a numeric type.

I do not think either of these steps should cause an error however you should ensure that the cost column is in either currency or decimal type just to be sure.

[vanessafvg]

@Kent_Heng so you want to remove all negative values?

in query editor, drop down the arrow on the column and under number filters select greater than and = 0, this will return values greater than and = 0 

[Kent_Heng]

Hi Vanessa, I wanted to remove the negative values, and its corresponding positive values. I will try Thomas's suggestion as below, and see if works! Thanks for your help 🙂