Compare Positive and Negative Rows in Same Column

[Anonymous]

Happy Friday,

Can anyone suggest me some idea? I want to compare Balance column rows,script should compare same positive and same negative number (Example:Compare postive 100 with -100 and remove those Rows).

Balance
100
200
300
400
-100
-200
-700
Result
Balance
300
400
-700

[Zubair_Muhammad]

@Anonymous

With Power Query, you can do something like this
Please see attached file for steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Balance", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ABS", each Number.Abs([Balance])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ABS"}, {{"Countrows", each Table.RowCount(_), type number}, {"ALL", each _, type table}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Balance"}, {"Balance"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ALL", each ([Countrows] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"ABS", "Countrows"})
in
    #"Removed Columns"

[Zubair_Muhammad]

@Anonymous

Also you can use following Calculated Table using DAX

From the Modelling Tab>>New Table

Calculated Table =
VAR temp1 =
    ADDCOLUMNS ( Table1, "ABS", ABS ( Table1[Balance] ) )
VAR temp2 =
    ADDCOLUMNS (
        temp1,
        "MyFilter", COUNTX ( FILTER ( temp1, [ABS] = EARLIER ( [ABS] ) ), 1 )
    )
VAR temp3 =
    FILTER ( temp2, [MyFilter] = 1 )
RETURN
    SELECTCOLUMNS ( temp3, "Balance", [Balance] )

[Anonymous]

Hi Zubair,

I just recieve my dataset and it has duplicate values in Balance Column but i found two columns Sign and Map.

Balance	Sign	Map
100	Y	USA
200	Y	USA
300	Y	China
400	Y	China
-100	N	USA
-200	N	USA
-700	N	China
100	Y	China

Balance	Sign	Map	Comments	Result
100	Y	USA	Same negative and posituve number and Sign should be Y&N and Region should be same	Delete
-100	N	USA	Same negative and posituve number and Sign should be Y&N and Region should be same	Delete
100	Y	China		Keep

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this Calculated table

Please see attached file with your sample data as well

Calculated Table 2 =
VAR temp1 =
    ADDCOLUMNS ( Table2, "ABS", ABS ( Table2[Balance] ) )
VAR temp2 =
    ADDCOLUMNS (
        temp1,
        "MyFilter", COUNTX (
            FILTER ( temp1, [ABS] = EARLIER ( [ABS] ) && [Map] = EARLIER ( [Map] ) ),
            1
        )
    )
VAR temp3 =
    FILTER ( temp2, [MyFilter] = 1 )
RETURN
    SELECTCOLUMNS ( temp3, "Balance", [Balance], "Map", [Map] )

I get this result

[Anonymous]

Thank you so much Zubair.You really saved my Days.