Forum Discussion
Positive & Negative duplicate values
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.
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"
- 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?
Are you trying with the code I posted above or are you trying to apply it to your own data?
- 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?
